﻿Option Explicit On
Imports Microsoft.VisualBasic
Imports System
Imports System.Xml
Imports System.Data
Imports System.IO
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Imports System.Globalization

Public Class ClassFunctionGetData
    Public Function Get_Booking(ByVal User_Name As String, ByVal Customer_no As String, ByVal Booking_no As String, ByVal Status As String, ByVal admin As Boolean, ByVal PORT As String, ByVal Vehicle_Text As String, ByVal MAgent_name As String, ByVal NotStatus As String, ByVal Booing_no_Search As String, ByVal Destination As String, ByVal DateFrom As String, ByVal Dateto As String, ByVal Product_name As String) As DataSet
        'Owner Booking คือ รหัสของคนที่ทำการขอแทน เมื่อเข้ามาดูจะสามารถดูรายการ Booking ที่ตนเองขอแทนได้
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT Top 200 Booking.*,Convert(DateTime,Booking.Date_From) as DateSort,Convert(DateTime,Booking.Booking_Date) as DateBookingSort,Status_IPORT.Status_Text "
        sql &= "    ,Wharf.Wharf,BookingWharf.Date_From as Date_Wharf,BookingWharf.Date_To as Date_WharfEnd,BookingWharf.TimeStart,BookingWharf.TimeEnd,ISNULL(dbo.BookingWharf.Confirm_Time, N'N') AS Confirm_Time,BookingWharf.Remark as RemarkWharf "
        sql &= " FROM  Status_IPORT INNER JOIN"
        sql &= " Booking ON Status_IPORT.Status_Code = Booking.Status LEFT OUTER JOIN "
        sql &= " Wharf INNER JOIN "
        sql &= "  BookingWharf ON Wharf.BookingWharf = BookingWharf.BookingWharf ON Booking.Booking_no = BookingWharf.Booking_no "
        sql &= " WHERE Booking.Booking_no <> '' "
        If Not (admin) Then
            If (User_Name <> "") Then
                sql &= " AND ((Booking.Booking_Code = @User_Name ) "
            End If
            If (Customer_no <> "") Then
                sql &= " OR (Booking.MAgent_no = @Customer_no) OR (Booking.SAgent_no = @Customer_no))"
            Else
                sql &= ")"
            End If

        End If
        If (Booking_no.Trim() <> "") Then
            sql &= " AND Booking.Booking_no = @Booking_no "
        End If
        If (Status.Trim() <> "") Then
            sql &= " AND Booking.Status in (" & Status & ") "
        End If
        If (PORT.Trim() <> "") Then
            sql &= " AND Booking.PORT = @PORT "
        End If
        If (Vehicle_Text.Trim() <> "") Then
            sql &= " AND Booking.Vehicle_Text Like '%' + @Vehicle_Text + '%' "
        End If
        If (MAgent_name.Trim() <> "") Then
            sql &= " AND Booking.MAgent_name Like '%' + @MAgent_name + '%' "
        End If
        If (Booing_no_Search.Trim() <> "") Then
            sql &= " AND Booking.Booking_no Like '%' + @Booking_no_S + '%'  "
        End If
        If (NotStatus.Trim() <> "") Then
            sql &= " and Booking.Status not in (" & NotStatus & ") "
        End If
        If (Destination.Trim() <> "A") Then
            sql &= " and Booking.Vehicle_Destination = @Destination "
        End If
        If (DateFrom <> "") And (Dateto <> "") Then
            sql &= " And ( CONVERT(VARCHAR(10), Convert(DateTime,Booking.Date_From) , 112) Between " & DateFrom.Trim() & " and " & Dateto.Trim() & " "
            sql &= " OR  CONVERT(VARCHAR(10), Convert(DateTime,Booking.Date_To) , 112) Between " & DateFrom.Trim() & " and " & Dateto.Trim() & " )"
        End If
        If (Product_name.Trim() <> "") Then
            sql &= " AND Booking.Product_name Like '%' + @Product + '%' "
        End If
        sql &= " ORDER BY Booking_no Desc "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@User_Name", SqlDbType.NVarChar, 50).Value = User_Name.Trim()
        adap.SelectCommand.Parameters.Add("@Customer_no", SqlDbType.NVarChar, 10).Value = Customer_no.Trim()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no.Trim()
        adap.SelectCommand.Parameters.Add("@Status", SqlDbType.NVarChar, 2).Value = Status.Trim()
        adap.SelectCommand.Parameters.Add("@PORT", SqlDbType.NVarChar, 1).Value = PORT.Trim()
        adap.SelectCommand.Parameters.Add("@Vehicle_Text", SqlDbType.NVarChar, 500).Value = Vehicle_Text.Trim()
        adap.SelectCommand.Parameters.Add("@MAgent_name", SqlDbType.NVarChar, 500).Value = MAgent_name.Trim()
        adap.SelectCommand.Parameters.Add("@Booking_no_S", SqlDbType.NVarChar, 10).Value = Booing_no_Search.Replace("*", "%").Trim()
        adap.SelectCommand.Parameters.Add("@Destination", SqlDbType.NVarChar, 1).Value = Destination.Trim()
        adap.SelectCommand.Parameters.Add("@Product", SqlDbType.NVarChar, 200).Value = Product_name.Trim()
        adap.Fill(ds, "Booking")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Shared Function CDateT(ByVal txtDate As String) As String
        Dim ret As String = ""
        Try
            ret = txtDate.Split("/")(1) & "/" & txtDate.Split("/")(0) & "/" & txtDate.Split("/")(2)
        Catch ex As Exception
            ret = txtDate
        End Try

        Return ret
    End Function

    Public Function Get_BookingPayment(ByVal MAgent_no As String) As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT Booking.*,Convert(DateTime,Booking.Date_From) as DateSort,Convert(DateTime,Booking.Booking_Date) as DateBookingSort,Status_IPORT.Status_Text "
        sql &= " FROM Booking,Status_IPORT"
        sql &= " WHERE Booking.Status = Status_IPORT.Status_Code "
        ' sql &= " AND Booking.MAgent_no = @MAgent_no"
        sql &= " AND Booking.Status = 'MO' "
        sql &= " AND Booking.Payin = '0' "
        sql &= " AND Booking.Credit_Limit <> 'Y'"
        sql &= " AND Booking.Status_Pay <> 'Y' "
        sql &= " AND Booking.Booking_no in (Select Distinct Booking_no from Temp_Estimate where Owner_Product = @Owner_Product)"
        sql &= " AND Booking.Booking_no Not in (select distinct booking_no from SAP_Advanced_Payment) "
        sql &= " ORDER BY DateSort,DateBookingSort Desc "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Owner_Product", SqlDbType.NVarChar, 10).Value = MAgent_no.Trim()
        adap.Fill(ds, "MAgent_no")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function Get_Tug_Report(ByVal DateFrom As String, ByVal DateTo As String, ByVal PORT As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select Distinct 'P' as Type_Event,'Cash in' as Type_Text, B.Booking_no,B.Vehicle_Destination,B.Vehicle_DWT,A.Data3,A.Data4,A.MATERIAL from Booking B,Actual_Data_Backup_For_Billing A,Booking_LogSheet_Out O " & vbCrLf
        sql &= " where CONVERT(VARCHAR(10), Convert(DateTime,O.Last_Date) , 112) Between " & DateFrom & " and " & DateTo & "   " & vbCrLf
        sql &= " AND O.Booking_no = B.Booking_no  " & vbCrLf
        sql &= " AND B.Status = 'FN' " & vbCrLf
        If (PORT <> "A") Then
            sql &= " AND B.PORT = '" & PORT & "' "
        End If
        sql &= " AND B.Booking_no = A.Booking_no  " & vbCrLf
        sql &= " and A.MATERIAL IN ('000000002100000236','000000002100000237') " & vbCrLf
        sql &= " Union  " & vbCrLf
        sql &= " select Distinct 'F' as Type_Event,'Internal Charge' as Type_Text, B.Booking_no,B.Vehicle_Destination,B.Vehicle_DWT,'' as Data3,'' As Data4,'' as MATERIAL from Booking B,Actual_Data_Backup_For_Billing A,Booking_LogSheet_Out O  " & vbCrLf
        sql &= " where CONVERT(VARCHAR(10), Convert(DateTime,O.Last_Date) , 112) Between " & DateFrom & " and " & DateTo & "   " & vbCrLf
        sql &= " AND O.Booking_no = B.Booking_no  " & vbCrLf
        sql &= " AND B.Status = 'FN' " & vbCrLf
        If (PORT <> "A") Then
            sql &= " AND B.PORT = '" & PORT & "' "
        End If
        sql &= " AND B.Booking_no = A.Booking_no  " & vbCrLf
        sql &= " and A.MATERIAL Not IN ('000000002100000236','000000002100000237') " & vbCrLf
        sql &= " and B.Booking_no Not In (select B.Booking_no from Booking B,Actual_Data_Backup_For_Billing A,Booking_LogSheet_Out O  " & vbCrLf
        sql &= " where CONVERT(VARCHAR(10), Convert(DateTime,O.Last_Date) , 112) Between " & DateFrom & " and " & DateTo & "   " & vbCrLf
        sql &= " AND O.Booking_no = B.Booking_no  " & vbCrLf
        sql &= " AND B.Status = 'FN' " & vbCrLf
        If (PORT <> "A") Then
            sql &= " AND B.PORT = '" & PORT & "' "
        End If
        sql &= " AND B.Booking_no = A.Booking_no  " & vbCrLf
        sql &= " and A.MATERIAL IN ('000000002100000236','000000002100000237')) " & vbCrLf
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.Fill(ds, "Tug_Report")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_BookingOneRecord(ByVal Booking_no As String, Optional ByVal MAgent_no As String = "") As DataSet
        'Owner Booking คือ รหัสของคนที่ทำการขอแทน เมื่อเข้ามาดูจะสามารถดูรายการ Booking ที่ตนเองขอแทนได้
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT Booking.*,Status_IPORT.Status_Text "
        sql &= " FROM Booking,Status_IPORT"
        sql &= " WHERE Booking.Status = Status_IPORT.Status_Code "
        If (Booking_no <> "") Then
            sql &= " AND Booking.Booking_no = @Booking_no "
        End If
        'If (MAgent_no <> "") Then
        '    sql &= " AND Booking.MAgent_no = @MAgent_no "
        'End If
        sql &= " ORDER BY Booking.Booking_no Desc "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no.Trim()
        'adap.SelectCommand.Parameters.Add("@MAgent_no", SqlDbType.NVarChar, 10).Value = MAgent_no.Trim() ' เพิ่มการตรวจสอบ MAgent ในกรณีที่ Booking เดียวกันแต่หลายเจ้าของ
        adap.Fill(ds, "Booking")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function Get_BookingFOrOTher(ByVal Booking_no As String, ByVal Vehicle_Text As String, ByVal Date_From As String, ByVal Date_To As String, ByVal Agent As String, ByVal PORT As String) As DataSet
        'Owner Booking คือ รหัสของคนที่ทำการขอแทน เมื่อเข้ามาดูจะสามารถดูรายการ Booking ที่ตนเองขอแทนได้
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT Booking.* "
        sql &= " FROM Booking "
        sql &= " WHERE Booking_no <> '' "
        If (Booking_no <> "") Then
            sql &= " AND Booking_no Like '%' + @Booking_no + '%' "
        End If
        If (Vehicle_Text <> "") Then
            sql &= " AND Vehicle_Text Like '%' + @Vehicle_Text + '%' "
        End If
        If (PORT <> "A") Then
            sql &= " AND PORT = @PORT "
        End If
        If (Agent <> "") Then
            sql &= " AND MAgent_name Like '%' + @Agent + '%' "
        End If
        If ((Date_From <> "") And (Date_To <> "")) Then
            'Format  YYYYMMDD
            sql &= " and ((((subString(Convert(varchar(10),Convert(DateTime,Booking.Date_From),120),1,4) + SubString(Convert(varchar(10),Convert(DateTime,Booking.Date_From),120),6,2) + SubString(Convert(varchar(10),Convert(DateTime,Booking.Date_From),120),9,2)) >= '" & Date_From & "') And ((subString(Convert(varchar(10),Convert(DateTime,Booking.Date_From),120),1,4) + SubString(Convert(varchar(10),Convert(DateTime,Booking.Date_From),120),6,2) + SubString(Convert(varchar(10),Convert(DateTime,Booking.Date_From),120),9,2)) <= '" & Date_To & "')) "
            sql &= " Or (((subString(Convert(varchar(10),Convert(DateTime,Booking.Date_To),120),1,4) + SubString(Convert(varchar(10),Convert(DateTime,Booking.Date_To),120),6,2) + SubString(Convert(varchar(10),Convert(DateTime,Booking.Date_To),120),9,2)) >= '" & Date_From & "') AND ((subString(Convert(varchar(10),Convert(DateTime,Booking.Date_To),120),1,4) + SubString(Convert(varchar(10),Convert(DateTime,Booking.Date_To),120),6,2) + SubString(Convert(varchar(10),Convert(DateTime,Booking.Date_To),120),9,2)) <= '" & Date_To & "')) "
            sql &= " Or (((subString(Convert(varchar(10),Convert(DateTime,Booking.Date_From),120),1,4) + SubString(Convert(varchar(10),Convert(DateTime,Booking.Date_From),120),6,2) + SubString(Convert(varchar(10),Convert(DateTime,Booking.Date_From),120),9,2)) >= '" & Date_From & "') AND ((subString(Convert(varchar(10),Convert(DateTime,Booking.Date_To),120),1,4) + SubString(Convert(varchar(10),Convert(DateTime,Booking.Date_To),120),6,2) + SubString(Convert(varchar(10),Convert(DateTime,Booking.Date_To),120),9,2)) <= '" & Date_To & "')) "
            sql &= " Or (((subString(Convert(varchar(10),Convert(DateTime,Booking.Date_From),120),1,4) + SubString(Convert(varchar(10),Convert(DateTime,Booking.Date_From),120),6,2) + SubString(Convert(varchar(10),Convert(DateTime,Booking.Date_From),120),9,2)) <= '" & Date_From & "') AND ((subString(Convert(varchar(10),Convert(DateTime,Booking.Date_To),120),1,4) + SubString(Convert(varchar(10),Convert(DateTime,Booking.Date_To),120),6,2) + SubString(Convert(varchar(10),Convert(DateTime,Booking.Date_To),120),9,2)) >= '" & Date_To & "'))) "
        End If
        sql &= " ORDER BY Booking.Booking_no DESC "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@PORT", SqlDbType.NVarChar, 1).Value = PORT.Trim()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no.Trim()
        adap.SelectCommand.Parameters.Add("@Vehicle_Text", SqlDbType.NVarChar, 500).Value = Vehicle_Text.Trim()
        adap.SelectCommand.Parameters.Add("@Agent", SqlDbType.NVarChar, 500).Value = Agent.Trim()
        adap.Fill(ds, "OTHER")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function Get_Booking_Wharf(ByVal Date_From As String, ByVal Date_To As String, ByVal PORT As String) As DataSet
        'Owner Booking คือ รหัสของคนที่ทำการขอแทน เมื่อเข้ามาดูจะสามารถดูรายการ Booking ที่ตนเองขอแทนได้
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        If (Date_From = "20000000") Then
            Date_From = "2000/01/01"
        End If
        If (Date_To = "20000000") Then
            Date_To = "2000/01/01"
        End If
        'Dim dFrom As Date = Date.ParseExact(Date_From, "yyyyMMdd", New CultureInfo("en-US"))
        'Dim dTo As Date = Date.ParseExact(Date_To, "yyyyMMdd", New CultureInfo("en-US"))
        'Date_From = dFrom.ToString("dd/MM/yyyy")
        'Date_To = dTo.ToString("dd/MM/yyyy")
        sql = "SELECT TOP 5 Booking.*,Status_IPORT.Status_Text "
        sql &= " FROM Booking,Status_IPORT"
        sql &= " WHERE Booking.Status = Status_IPORT.Status_Code "
        sql &= " AND Booking.Status Not in ('CC','CV','CR','CM','CA')"
        sql &= " AND Booking.PORT = @PORT "
        If ((Date_From <> "") And (Date_To <> "")) Then
            'Format  YYYYMMDD
            sql &= " and ( "

            sql &= String.Format("DATEDIFF(DAY, CONVERT(datetime,'{0}',103),CONVERT(datetime,Date_From,103)) >= 0", Date_From)
            sql &= String.Format(" AND DATEDIFF(DAY, CONVERT(datetime,Date_To,103),CONVERT(datetime,'{0}',103)) >=0", Date_To)
           sql &= ")"
            sql &= " AND Booking.Booking_no Not in (Select Booking_no From BookingWharf,Wharf WHERE Wharf.PORT = @PORT and Wharf.BookingWharf = BookingWharf.BookingWharf )"
           End If
        sql &= " ORDER BY Booking.Booking_no Desc "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@PORT", SqlDbType.NVarChar, 1).Value = PORT
        adap.Fill(ds, "Booking_Wharf")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function Get_Booking_WharfQueue(ByVal Date_From As String, ByVal Date_To As String, ByVal Wharf As String) As DataSet
        'Owner Booking คือ รหัสของคนที่ทำการขอแทน เมื่อเข้ามาดูจะสามารถดูรายการ Booking ที่ตนเองขอแทนได้
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String = ""
        Dim ds As New DataSet
        'sql = " Select Booking.*,BookingWharf.BookingWharf,BookingWharf.Date_From as D_From,BookingWharf.Date_To as D_To,BookingWharf.Confirm_Time,BookingWharf.TimeStart,BookingWharf.TimeEnd,BookingWharf.Remark as Wharf_Remark From BookingWharf,Booking "
        'sql &= " WHERE Booking.Booking_no = BookingWharf.Booking_no AND Booking.Status Not in ('CC','CM','CR','CV') "
        'sql &= " And  (subString(BookingWharf.DateStart,7,4) + SubString(BookingWharf.DateStart,4,2) + SubString(BookingWharf.DateStart,1,2)) Between " & Date_From & " and " & Date_To & " "
        'sql &= " AND (subString(BookingWharf.DateEnd,7,4) + SubString(BookingWharf.DateEnd,4,2) + SubString(BookingWharf.DateEnd,1,2)) Between " & Date_From & " and " & Date_To & " "
        'If (Wharf <> "") Then
        '    sql &= " AND BookingWharf.BookingWharf = @BookingWharf"
        'End If

        Dim db As New DB_Class()

        Dim dateFrom As Date = Date.ParseExact(Date_From, "yyyyMMdd", New CultureInfo("en-US"))
        Dim dateTo As Date = Date.ParseExact(Date_To, "yyyyMMdd", New CultureInfo("en-US"))

        Dim dateFromStr As String = dateFrom.ToString("yyyy-MM-dd")
        Dim dateToStr As String = dateTo.ToString("yyyy-MM-dd")


        db.queryString = "select b.*,bw.BookingWharf,bw.Date_from as D_From,bw.Date_To as D_To,bw.Confirm_Time,bw.TimeStart,'' as Shifting_ID "
        db.queryString &= " ,bw.TimeEnd,bw.Remark as Wharf_Remark,bw.Note,bw.Booking_no as WBooking_no"
        db.queryString &= " ,CONVERT(DATE,bw.Date_From,103) as D_FromSort"
        db.queryString &= " from BookingWharf bw left outer join Booking b on bw.Booking_no=b.Booking_no"
        db.queryString &= " where b.Status Not in ('CC','CM','CR','CV','CA')"
        db.queryString &= " and ("
        db.queryString &= " CONVERT(DATE,bw.DateStart,103) Between '" & dateFromStr & "' and '" & dateToStr & "'"
        db.queryString &= " or CONVERT(DATE,bw.DateEnd,103) Between '" & dateFromStr & "' and '" & dateToStr & "'"
        db.queryString &= " or '" & dateFromStr & "'  Between CONVERT(DATE,bw.DateStart,103) and CONVERT(DATE,bw.DateEnd,103)"
        db.queryString &= " or '" & dateToStr & "' Between CONVERT(DATE,bw.DateStart,103) and CONVERT(DATE,bw.DateEnd,103)"
        db.queryString &= ")"
        If (Wharf <> "") Then
            db.queryString &= " AND bw.BookingWharf = " & Wharf & ""
        End If
        Dim dt As DataTable = db.getTable("Booking_Wharf_Queue")

        '''''''''''''''''''''''''''''''''''''''''''''''''''
        '''''Data From Booking_Shifting Table''''''''''''''
        '''''''''''''''''''''''''''''''''''''''''''''''''''

        db.queryString = " select b.*"
        db.queryString &= " ,bs.Bookingwharf,CONVERT(varchar(20),CONVERT(DATE,bs.DateStart,103),106) as D_From,bs.Shifting_ID"
        db.queryString &= ",CONVERT(varchar(20),CONVERT(DATE,bs.DateEnd,103),106) as D_To,bs.Confirm_Time,bs.TimeStart"
        db.queryString &= ",bs.TimeEnd,bs.Remark as Wharf_Remark,bs.Note,bs.Booking_no as WBooking_no"
        db.queryString &= ",CONVERT(DATE,bs.DateStart,103) as D_FromSort"
        db.queryString &= " from Booking_Shifting bs left outer join Booking b on bs.Booking_no=b.Booking_no"
        db.queryString &= " where b.Status Not in ('CC','CM','CR','CV','CA')"
        db.queryString &= " and ("
        db.queryString &= " CONVERT(DATE,bs.DateStart,103) Between '" & dateFromStr & "' and '" & dateToStr & "'"
        db.queryString &= " or CONVERT(DATE,bs.DateEnd,103) Between '" & dateFromStr & "' and '" & dateToStr & "'"
        db.queryString &= " or '" & dateFromStr & "'  Between CONVERT(DATE,bs.DateStart,103) and CONVERT(DATE,bs.DateEnd,103)"
        db.queryString &= " or '" & dateToStr & "' Between CONVERT(DATE,bs.DateStart,103) and CONVERT(DATE,bs.DateEnd,103)"
        db.queryString &= ")"
        If (Wharf <> "") Then
            db.queryString &= " AND bs.BookingWharf = " & Wharf & ""
        End If
        Dim dtShifting As DataTable = db.getTable("Shifting")
        For Each drShifting As DataRow In dtShifting.Rows
            dt.ImportRow(drShifting)
        Next


        ds.Tables.Add(dt.Copy())
        Return ds
        'ds.Tables.Add(dt.Copy())
    End Function

    Public Function Get_BookingShifting(ByVal Booking_no As String) As DataSet
        'Owner Booking คือ รหัสของคนที่ทำการขอแทน เมื่อเข้ามาดูจะสามารถดูรายการ Booking ที่ตนเองขอแทนได้
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT   dbo.Booking_Shifting.*,  dbo.Booking.Booking_no, dbo.Booking_Shifting.Shifting_ID, dbo.Booking_Shifting.BookingWharf, dbo.Booking_Shifting.DateStart, dbo.Booking_Shifting.DateEnd, " & vbCrLf
        sql &= "        dbo.Booking_Shifting.TimeStart, dbo.Booking_Shifting.TimeEnd, dbo.Booking_Shifting.Remark,dbo.Booking_Shifting.Product_no" & vbCrLf
        sql &= "FROM         dbo.Booking LEFT OUTER JOIN" & vbCrLf
        sql &= "                      dbo.Booking_Shifting ON dbo.Booking.Booking_no = dbo.Booking_Shifting.Booking_No" & vbCrLf
        sql &= " WHERE dbo.Booking.Booking_no <> '' " & vbCrLf
        If (Booking_no.Trim() <> "") Then
            sql &= " AND dbo.Booking.Booking_no Like '%' + @Booking_no + '%' " & vbCrLf
        End If
        sql &= "ORDER BY Booking.Booking_no DESC, dbo.Booking_Shifting.Shifting_ID ASC"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no.Trim()
        adap.Fill(ds, "Shifting")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function Get_BookingShiftingNewRow(ByVal Booking_no As String) As DataSet
        'Owner Booking คือ รหัสของคนที่ทำการขอแทน เมื่อเข้ามาดูจะสามารถดูรายการ Booking ที่ตนเองขอแทนได้
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet

        sql = "SELECT     dbo.Booking.Booking_no, dbo.Booking_Shifting.Shifting_ID, dbo.Booking_Shifting.BookingWharf, dbo.Booking_Shifting.DateStart, dbo.Booking_Shifting.DateEnd, " & vbCrLf
        sql &= "        dbo.Booking_Shifting.TimeStart, dbo.Booking_Shifting.TimeEnd, dbo.Booking_Shifting.Remark,dbo.Booking_Shifting.Product_no,dbo.Booking_Shifting.Product_volume" & vbCrLf
        sql &= "FROM         dbo.Booking LEFT OUTER JOIN" & vbCrLf
        sql &= "                      dbo.Booking_Shifting ON dbo.Booking.Booking_no = dbo.Booking_Shifting.Booking_No" & vbCrLf
        sql &= " WHERE dbo.Booking.Booking_no <> '' " & vbCrLf
        If (Booking_no.Trim() <> "") Then
            sql &= " AND dbo.Booking.Booking_no Like '%' + @Booking_no + '%' " & vbCrLf
        End If

        sql &= "UNION" & vbCrLf

        sql &= "SELECT     '' AS Booking_no, '' AS Shifting_ID, '' AS BookingWharf, NULL AS DateStart, NULL AS DateEnd, " & vbCrLf
        sql &= "        '' AS TimeStart, '' AS TimeEnd, '' AS Remark,'' AS Product_no,'' AS Product_volume" & vbCrLf
        sql &= "FROM         dbo.Booking LEFT OUTER JOIN" & vbCrLf
        sql &= "                      dbo.Booking_Shifting ON dbo.Booking.Booking_no = dbo.Booking_Shifting.Booking_No" & vbCrLf
        sql &= " WHERE dbo.Booking.Booking_no <> '' " & vbCrLf
        If (Booking_no.Trim() <> "") Then
            sql &= " AND dbo.Booking.Booking_no Like '%' + @Booking_no + '%' " & vbCrLf
        End If

        sql &= "ORDER BY Booking.Booking_no DESC, dbo.Booking_Shifting.Shifting_ID ASC" & vbCrLf

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no.Trim()
        adap.Fill(ds, "Shifting")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function Get_Holiday(ByVal Day As String, ByVal Month As String, ByVal Year As String) As Boolean
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * From Holiday "
        sql &= " where Day = @Day "
        sql &= " and Month = @Month "
        sql &= " and Year = @Year "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Day", SqlDbType.NVarChar, 2).Value = Day
        adap.SelectCommand.Parameters.Add("@Month", SqlDbType.NVarChar, 2).Value = Month
        adap.SelectCommand.Parameters.Add("@Year", SqlDbType.NVarChar, 4).Value = Year
        adap.Fill(ds, "Holiday")
        adap = Nothing
        con.Close()
        If (ds.Tables(0).Rows.Count > 0) Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function Get_NameInputOperation(ByVal Booking_no As String, ByVal Role_no As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select Action_Operation.A_By,Action_Operation.A_Date,Action_Operation.A_Time,Action_Operation.Booking_no "
        sql &= " from Action_Operation,User_Role "
        sql &= " where User_Role.Role_no = @Role_no"
        sql &= " and User_Role.User_name = Action_Operation.A_Username "
        sql &= " and Action_Operation.Booking_no = @Booking_no " & vbCrLf
        sql &= " order by Action_Operation.A_Code desc " & vbCrLf
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.SelectCommand.Parameters.Add("@Role_no", SqlDbType.NVarChar, 10).Value = Role_no
        adap.Fill(ds, "Operation_Name")
        adap = Nothing
        con.Close()
        If (ds.Tables(0).Rows.Count > 0) Then
            Return ds.Tables(0).Rows(0).Item("A_By") & "$" & ds.Tables(0).Rows(0).Item("A_Date") & " (" & ds.Tables(0).Rows(0).Item("A_Time") & ")"
        Else
            Return ""
        End If
    End Function

    Public Function Get_Booking_LOG(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select TOP 1 * from Booking_Log "
        sql &= " where Booking_no = @Booking_no "
        sql &= " ORDER By Booking_Log Desc"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Daily_LCT(ByVal Booking_no As String, ByVal Product_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Daily_LCT "
        sql &= " where Booking_no = @Booking_no "
        sql &= " and Product_no = @Product_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.SelectCommand.Parameters.Add("@Product_no", SqlDbType.NVarChar, 10).Value = Product_no
        adap.Fill(ds, "Daily_LCT")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_BookingWharf_ForMYBooking(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select Wharf.Wharf,BookingWharf.Date_From,BookingWharf.Date_To,BookingWharf.Timestart,BookingWharf.TimeEnd from BookingWharf,Wharf "
        sql &= " where BookingWharf.BookingWharf = Wharf.BookingWharf "
        sql &= " And Booking_no = @Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Booking_For_MAT_FREE_REPORT(ByVal Destination As String, ByVal DateFrom As String, ByVal Date_To As String, ByVal PORT As String, ByVal Agent As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select Booking.Booking_no,Booking.Vehicle_Text,Booking.Vehicle_GRT,Booking.Vehicle_DWT,Booking.Vehicle_LOA,Booking.Vehicle_Draft,Booking.PORT,Booking.MAgent_no,Booking.MAgent_name from Booking,Load_Unload_Vessel "
        sql &= " where Booking.Status = 'FN'"
        sql &= " and Booking.Booking_no Not in (select Shift_Booking as Booking_no from BookingWharf where Shift_Booking <> '')"
        If (PORT = "B") Then
            sql &= " AND Booking.PORT = 'B' "
        Else
            sql &= " AND Booking.PORT = 'L' "
            sql &= " AND Booking.Vehicle_Destination = '" & Destination & "' "
        End If
        If (Agent <> "") Then
            sql &= " AND ((Booking.MAgent_no Like '%' + @Agent + '%') OR (Booking.MAgent_name Like '%' + @Agent + '%') )"
        End If
        sql &= " AND Booking.Booking_no = Load_Unload_Vessel.Booking_no "
        sql &= " And  '20' + subString(Depart_Actual_Date,7,2) + subString(Depart_Actual_Date,4,2) + subString(Depart_Actual_Date,1,2) Between " & DateFrom & " and " & Date_To & " "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Agent", SqlDbType.NVarChar, 200).Value = Agent
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Booking_For_MAT_FREE_REPORT_ItemMonthly(ByVal Booking_no As String, ByVal SOldto As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * "
        sql &= " from Actual_Data_Backup_For_Billing,Booking "
        sql &= " where Actual_Data_Backup_For_Billing.Booking_no = @Booking_no"
        sql &= " and Actual_Data_Backup_For_Billing.chk <> 'N' "
        If (SOldto <> "") Then
            sql &= " and Actual_Data_Backup_For_Billing.Owner = @Owner "
        End If
        sql &= " and Actual_Data_Backup_For_Billing.Booking_no = Booking.Booking_no "

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.SelectCommand.Parameters.Add("@Owner", SqlDbType.NVarChar, 10).Value = SOldto
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Booking_For_MAT_FREE_REPORT_Item(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * "
        sql &= " from Actual_Data_Backup_For_Billing,Booking "
        sql &= " where Actual_Data_Backup_For_Billing.Booking_no = @Booking_no"
        sql &= " and Actual_Data_Backup_For_Billing.Booking_no = Booking.Booking_no "

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Booking_Vessel_Size_LCT(ByVal Year As String, ByVal Port As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select Substring(Depart_Actual_Date,4,2) as Month,Booking.Booking_no, Booking.Vehicle_DWT,Booking.Vehicle_Destination from Booking,Load_Unload_Vessel  "
        sql &= " where '20' + Substring(Depart_Actual_Date,7,2) = '" & Year & "'"
        sql &= "  and Load_Unload_Vessel.Booking_no = Booking.Booking_no "
        sql &= " and Booking.PORT = '" & Port & "' "
        sql &= " Order by Month "

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.Fill(ds, "Size")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Booking_For_MAT_FREE_REPORT_Item_By_Material_Get_FOR_BULK(ByVal Booking_no As String, ByVal Material As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * "
        sql &= " from Actual_Data_Backup_For_Billing "
        sql &= " where Booking_no = @Booking_no"
        sql &= " and Material = @Material "

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.SelectCommand.Parameters.Add("@Material", SqlDbType.NVarChar, 18).Value = Material
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        If (ds.Tables(0).Rows.Count > 0) Then
            If (ds.Tables(0).Rows(0).Item("chk") & "" = "") Then
                Return "Y"
            Else
                Return ds.Tables(0).Rows(0).Item("chk") & ""
            End If
        Else
            Return "Y"
        End If
    End Function

    Public Function Get_BookingMarketing(ByVal Booking_no As String, ByVal Status As String, ByVal _Actual As Boolean, ByVal Ship_name As String, ByVal PORT As String, ByVal MAgent_name As String, ByVal Type As String, ByVal NotStatus As String, ByVal DateFrom As String, ByVal DateTo As String) As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String = "SELECT TOP 200 * From ( "
        Dim ds As New DataSet
        If (Type = "0") Or (Type = "1") Then
            sql &= " select Distinct Booking.Booking_no, Booking.Booking_Code, Booking.Booking_name, Booking.MAgent_no, Booking.MAgent_name, Booking.SAgent_no, Booking.SAgent_name,  " & vbCrLf
            sql &= "   Booking.Vehicle_no, Booking.Vehicle_Text, Booking.Vehicle_LOA, Booking.Vehicle_DWT, Booking.Vehicle_Draft, Booking.Vehicle_GRT, Booking.Vehicle_CNTRY,  " & vbCrLf
            sql &= "   Booking.Booking_Date, Booking.Booking_Time, Booking.Date_From, Booking.Date_To, Booking.PORT, Booking.Vehicle_Destination, Booking.Vehicle_TypeBuy,  " & vbCrLf
            sql &= "  Convert(nvarchar(4000),Booking.Remark) as Remark, Booking.Status, Booking.Marketing_approve, Booking.Port_no, Convert(nvarchar(4000),Booking.Remark_VPS) as Remark_VPS, Convert(nvarchar(4000),Booking.Remark_MRI) as Remark_MRI, Booking.VPS_approve,  " & vbCrLf
            sql &= "  Booking.MRI_approve, Booking.Product_name, Booking.Product_Amount, Booking.Product_Unit, Booking.BUYER, Booking.SELLER, Booking.File_Upload,  " & vbCrLf
            sql &= "  Booking.Lay_Time, Booking.MKT_Product, Booking.MKT_Custom, Booking.MKT_Finance, Booking.MKT_Approve, Booking.BunkerFO_chk, Booking.BunkerDO_chk,  " & vbCrLf
            sql &= "  Booking.BunkerFO_Point, Booking.BunkerDO_Point, Booking.BunkerDetail, Booking.Water_Point, Booking.Cancel_By, Booking.Cancel_Description,  " & vbCrLf
            sql &= "  Booking.Vps_Acknowledge, Booking.MKT_Acknowledge, Booking.Payin, Booking.Bill_To_no, Booking.Bill_To_Name, Booking.StandardTime, Booking.Credit_Limit,  " & vbCrLf
            sql &= "  Booking.Status_Pay, Booking.Demurrage, Booking.Demurrage_Money, Booking.Electricity, Booking.Shifting,Booking.MAgent_no as Owner_Product_no,Booking.MAgent_name as Owner_Product_name,'' as Type_Pay_Product " & vbCrLf
            sql &= " from Booking " & vbCrLf
            sql &= " where Booking.Booking_no <> '' " & vbCrLf

            sql &= " and Booking.Booking_no Not in (select Shift_Booking as Booking_no from BookingWharf where Shift_Booking <> '')"

            If (Booking_no.Trim() <> "") Then
                sql &= " and Booking.Booking_no Like '%' + @Booking_no + '%'  " & vbCrLf
            End If
            If (Ship_name.Trim() <> "") Then
                sql &= " and Booking.Vehicle_Text Like '%' + @Vehicle_Text + '%' "
            End If
            If (PORT.Trim() <> "0") Then
                sql &= " and Booking.PORT = @PORT "
            End If
            If (MAgent_name.Trim() <> "") Then
                sql &= " and Booking.MAgent_name Like '%' + @MAgent_name  + '%' "
            End If
            If (Status.Trim() <> "") Then
                sql &= " and Booking.Status in (" & Status & ") "
            End If
            If (NotStatus.Trim() <> "") Then
                sql &= " and Booking.Status not in (" & NotStatus & ") "
            End If
            If (DateFrom <> "") And (DateTo <> "") Then
                sql &= " And ( CONVERT(VARCHAR(10), Convert(DateTime,Booking.Date_From) , 112) Between " & DateFrom & " and " & DateTo & " "
                sql &= " OR  CONVERT(VARCHAR(10), Convert(DateTime,Booking.Date_To) , 112) Between " & DateFrom & " and " & DateTo & " )"
            End If

            sql &= " Union  " & vbCrLf

            sql &= " select Booking.Booking_no, Booking.Booking_Code, Booking.Booking_name, Booking.MAgent_no, Booking.MAgent_name, Booking.SAgent_no, Booking.SAgent_name,  " & vbCrLf
            sql &= "  Booking.Vehicle_no, Booking.Vehicle_Text, Booking.Vehicle_LOA, Booking.Vehicle_DWT, Booking.Vehicle_Draft, Booking.Vehicle_GRT, Booking.Vehicle_CNTRY,  " & vbCrLf
            sql &= " Booking.Booking_Date, Booking.Booking_Time, Booking.Date_From, Booking.Date_To, Booking.PORT, Booking.Vehicle_Destination, Booking.Vehicle_TypeBuy,  " & vbCrLf
            sql &= "  Convert(nvarchar(4000),Booking.Remark) as Remark, Booking_Product.Status,Booking_Product.Type_Pay as Marketing_approve, Booking.Port_no, Convert(nvarchar(4000),Booking.Remark_VPS) as Remark_VPS, Convert(nvarchar(4000),Booking.Remark_MRI) as Remark_MRI, Booking.VPS_approve,  " & vbCrLf
            sql &= "  Booking.MRI_approve, Booking.Product_name, Booking.Product_Amount, Booking.Product_Unit, Booking.BUYER, Booking.SELLER, Booking.File_Upload,  " & vbCrLf
            sql &= "  Booking.Lay_Time, Booking.MKT_Product, Booking.MKT_Custom, Booking.MKT_Finance, Booking.MKT_Approve, Booking.BunkerFO_chk, Booking.BunkerDO_chk,  " & vbCrLf
            sql &= "  Booking.BunkerFO_Point, Booking.BunkerDO_Point, Booking.BunkerDetail, Booking.Water_Point, Booking.Cancel_By, Booking.Cancel_Description,  " & vbCrLf
            sql &= "  Booking.Vps_Acknowledge, Booking.MKT_Acknowledge,Booking_Product.Type_Pay as Payin, Booking.Bill_To_no, Booking.Bill_To_Name, Booking.StandardTime, Booking_Product.Credit_Limit,  " & vbCrLf
            sql &= "  Booking.Status_Pay, Booking.Demurrage, Booking.Demurrage_Money, Booking.Electricity, Booking.Shifting,Booking_Product.Owner_Product_no,Booking_Product.Owner_Product_name,Booking_Product.Type_Pay as Type_Pay_Product" & vbCrLf
            sql &= " FROM   Booking LEFT OUTER JOIN " & vbCrLf
            sql &= "  Booking_Product ON Booking.Booking_no = Booking_Product.Booking_no " & vbCrLf
            sql &= " where Booking.Booking_no <> '' " & vbCrLf
            sql &= " and Booking.MAgent_no <> Booking_Product.Owner_Product_no"

            sql &= " and Booking.Booking_no Not in (select Shift_Booking as Booking_no from BookingWharf where Shift_Booking <> '')"

            sql &= " and NOT( Booking_Product.Owner_Product_no is null) "
            If (Booking_no.Trim() <> "") Then
                sql &= " and Booking.Booking_no Like '%' + @Booking_no + '%' " & vbCrLf
            End If
            If (Ship_name.Trim() <> "") Then
                sql &= " and Booking.Vehicle_Text Like '%' + @Vehicle_Text + '%' "
            End If
            If (PORT.Trim() <> "0") Then
                sql &= " and Booking.PORT = @PORT "
            Else
                sql &= " and Booking.PORT = 'B' "
            End If
            If (MAgent_name.Trim() <> "") Then
                sql &= " and Booking.MAgent_name Like '%' + @MAgent_name  + '%' "
            End If
            If (Status.Trim() <> "") Then
                sql &= " and Booking.Status in (" & Status & ") "
            End If
            If (NotStatus.Trim() <> "") Then
                sql &= " and Booking.Status not in (" & NotStatus & ") "
            End If
            If (DateFrom <> "") And (DateTo <> "") Then
                sql &= " And ( CONVERT(VARCHAR(10), Convert(DateTime,Booking.Date_From) , 112) Between " & DateFrom & " and " & DateTo & " "
                sql &= " OR  CONVERT(VARCHAR(10), Convert(DateTime,Booking.Date_To) , 112) Between " & DateFrom & " and " & DateTo & " )"
            End If
        End If
        If (Type = "0") Or (Type = "2") Then
            If (_Actual) Then
                If (sql <> "") And (sql <> "SELECT TOP 200 * From ( ") Then
                    sql &= " UNION " & vbCrLf
                End If
                sql &= " select 'B' + BV_no as Booking_no, '' as Booking_Code, '' as Booking_name, '' as MAgent_no, '' as MAgent_name, '' as SAgent_no, '' as SAgent_name,   " & vbCrLf
                sql &= "  '' as Vehicle_no, 'Barge Vessel' as Vehicle_Text, '' as Vehicle_LOA, '' as Vehicle_DWT, '' as Vehicle_Draft, '' as Vehicle_GRT, '' as Vehicle_CNTRY,   " & vbCrLf
                sql &= "  '' as Booking_Date, '' as Booking_Time, '' as Date_From, '' as Date_To, 'B' as PORT, '' as Vehicle_Destination, '' as Vehicle_TypeBuy,   " & vbCrLf
                sql &= "  '' as Remark, Complete as Status, '' as Marketing_approve, '' as Port_no, '' as Remark_VPS ,''  as Remark_MRI, '' as VPS_approve,   " & vbCrLf
                sql &= "  '' as MRI_approve, '' as Product_name, '' as Product_Amount, '' as Product_Unit, '' as BUYER, '' as SELLER, '' as File_Upload,   " & vbCrLf
                sql &= "  '' as Lay_Time, '' as MKT_Product, '' as MKT_Custom, '' as MKT_Finance, '' as MKT_Approve, '' as BunkerFO_chk, '' as BunkerDO_chk,   " & vbCrLf
                sql &= "  '' as BunkerFO_Point, '' as BunkerDO_Point, '' as BunkerDetail, '' as Water_Point, '' as Cancel_By, '' as Cancel_Description,   " & vbCrLf
                sql &= "  '' as Vps_Acknowledge, '' as MKT_Acknowledge, '' as Payin, '' as Bill_To_no, '' as Bill_To_Name, '' as StandardTime, '' as Credit_Limit,   " & vbCrLf
                sql &= "  '' as Status_Pay, '' as Demurrage, '' as Demurrage_Money, '' as Electricity, '' as Shifting,''  as Owner_Product_no,Company  as Owner_Product_name,'' as Type_Pay_Product  " & vbCrLf
                sql &= "   from Booking_Bas_Vessel " & vbCrLf
                sql &= "  where BV_no <> ''"
                If (Booking_no.Trim() <> "") Then
                    sql &= " and BV_no Like '%' + @Booking_no + '%' " & vbCrLf
                End If
                If (Ship_name.Trim() <> "") Then
                    sql &= " AND 'Barge Vessel' LIKE '%' + @Vehicle_Text + '%' "
                End If
                If (MAgent_name.Trim() <> "") Then
                    sql &= " AND '' LIKE '%' + @MAgent_name  + '%' "
                End If
                If (Status.Trim() = "'FN'") Then
                    sql &= "  and Complete= 'F' " & vbCrLf
                ElseIf (Status.Trim() = "'OA'") Then
                    sql &= "  and Complete <> 'F' " & vbCrLf
                End If

            End If
        End If
        If (Type = "0") Or (Type = "3") Then
            'Other Service
            If (_Actual) Then
                If (sql <> "") Then
                    sql &= " UNION " & vbCrLf
                End If
                sql &= " select 'S' + Ots_ID as Booking_no, '' as Booking_Code, '' as Booking_name, '' as MAgent_no, Customer_name as MAgent_name, '' as SAgent_no, '' as SAgent_name,   " & vbCrLf
                sql &= "  '' as Vehicle_no, 'Other Service' as Vehicle_Text, '' as Vehicle_LOA, '' as Vehicle_DWT, '' as Vehicle_Draft, '' as Vehicle_GRT, '' as Vehicle_CNTRY,   " & vbCrLf
                sql &= "  '' as Booking_Date, '' as Booking_Time, '' as Date_From, '' as Date_To, PORT as PORT, '' as Vehicle_Destination, '' as Vehicle_TypeBuy,   " & vbCrLf
                sql &= "  '' as Remark, Status, '' as Marketing_approve, '' as Port_no, '' as Remark_VPS ,''  as Remark_MRI, '' as VPS_approve,   " & vbCrLf
                sql &= "  '' as MRI_approve, '' as Product_name, '' as Product_Amount, '' as Product_Unit, '' as BUYER, '' as SELLER, '' as File_Upload,   " & vbCrLf
                sql &= "  '' as Lay_Time, '' as MKT_Product, '' as MKT_Custom, '' as MKT_Finance, '' as MKT_Approve, '' as BunkerFO_chk, '' as BunkerDO_chk,   " & vbCrLf
                sql &= "  '' as BunkerFO_Point, '' as BunkerDO_Point, '' as BunkerDetail, '' as Water_Point, '' as Cancel_By, '' as Cancel_Description,   " & vbCrLf
                sql &= "  '' as Vps_Acknowledge, '' as MKT_Acknowledge, '' as Payin, '' as Bill_To_no, '' as Bill_To_Name, '' as StandardTime, '' as Credit_Limit,   " & vbCrLf
                sql &= "  '' as Status_Pay, '' as Demurrage, '' as Demurrage_Money, '' as Electricity, '' as Shifting,''  as Owner_Product_no,Customer_name  as Owner_Product_name,'' as Type_Pay_Product  " & vbCrLf
                sql &= "   from OtherService_Head " & vbCrLf
                sql &= "  where Ots_ID <> ''"
                If (Booking_no.Trim() <> "") Then
                    sql &= " and Ots_ID Like '%' + @Booking_no + '%' " & vbCrLf
                End If
                If (Ship_name.Trim() <> "") Then
                    sql &= " AND 'Other Service' LIKE '%' + @Vehicle_Text + '%' "
                End If
                If (MAgent_name.Trim() <> "") Then
                    sql &= " AND Customer_name LIKE '%' + @MAgent_name  + '%' "
                End If
                If (Status.Trim() = "'FN'") Then
                    sql &= " and Status = 'F' " & vbCrLf
                ElseIf (Status.Trim() = "'OA'") Then
                    sql &= " and Status <> 'F' " & vbCrLf
                End If
                ' sql &= "    where Status = 'N' " & vbCrLf
            End If
        End If

        sql &= " ) as tb1 Order by Booking_no Desc " & vbCrLf

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no.Trim()
        adap.SelectCommand.Parameters.Add("@Vehicle_Text", SqlDbType.NVarChar, 500).Value = Ship_name.Trim()
        adap.SelectCommand.Parameters.Add("@PORT", SqlDbType.NVarChar, 1).Value = PORT.Trim()
        adap.SelectCommand.Parameters.Add("@MAgent_name", SqlDbType.NVarChar, 500).Value = MAgent_name.Trim()
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function Get_Transection(ByVal MAgent_no As String, ByVal Doc_Date As String, ByVal Type_Pay As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select top 100 * from ( select Distinct A.EX_BELNR,M.Customer as MAgent_no,C.Name1 + ' ' + C.Name2 + ' ' + C.Name3 as MAgent_name,B.Vehicle_Text,A.GL_ACCOUNT,A.Type_Pay,A.Amount,B.Booking_no,A.WHT,A.Vat,A.DOC_DATE  " & vbCrLf
        sql &= "  from SAP_Advanced_Payment A,Booking B,SAP_Advanced_Payment_Material M,Customer C " & vbCrLf
        sql &= " where A.Ex_Belnr Like '213%' " & vbCrLf
        sql &= " and A.Booking_no = B.Booking_no " & vbCrLf
        sql &= " and A.Iport_no = M.Iport_no " & vbCrLf
        sql &= " and ('0000' + M.Customer) = C.Customer_no " & vbCrLf

        If (MAgent_no <> "") Then
            sql &= " and ((B.MAgent_no Like '%' + @MAgent_no + '%') OR (B.MAgent_name Like '%' + @MAgent_no + '%')) "
        End If
        If (Doc_Date <> "") Then
            sql &= " and A.DOC_DATE = @DOC_DATE "
        End If
        If (Type_Pay <> "A") Then
            sql &= " and A.Type_Pay = @Type_Pay "
        End If

        sql &= " Union " & vbCrLf
        sql &= " select Distinct I.EX_BELNR,B.MAgent_no,B.MAgent_name,B.Vehicle_Text,I.GL_ACCOUNT,I.Type_Pay,I.AMOUNT,B.Booking_no,I.WHT,'Vat' As Vat,I.DOC_DATE " & vbCrLf
        sql &= " from SAP_INCOME I,Booking B " & vbCrLf
        sql &= " where I.Ex_Belnr Like '214%' " & vbCrLf
        sql &= " and I.Booking_no = B.Booking_no " & vbCrLf

        If (MAgent_no <> "") Then
            sql &= " and ((B.MAgent_no Like '%' + @MAgent_no + '%') OR (B.MAgent_name Like '%' + @MAgent_no + '%')) "
        End If
        If (Doc_Date <> "") Then
            sql &= " and I.DOC_DATE = @DOC_DATE "
        End If
        If (Type_Pay <> "A") Then
            sql &= " and I.Type_Pay = @Type_Pay "
        End If
        sql &= "  ) as table1 ORDER BY Doc_Date Desc, EX_BELNR Desc"

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@MAgent_no", SqlDbType.NVarChar, 200).Value = MAgent_no.Trim()
        adap.SelectCommand.Parameters.Add("@DOC_DATE", SqlDbType.NVarChar, 8).Value = Doc_Date
        adap.SelectCommand.Parameters.Add("@Type_Pay", SqlDbType.NVarChar, 1).Value = Type_Pay
        adap.Fill(ds, "Transection")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_ProductEvent(ByVal Booking_no As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select TOP 1 * from Booking_Product "
        sql &= " where Booking_no = @Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "Product")
        adap = Nothing
        con.Close()
        If (ds.Tables(0).Rows.Count > 0) Then
            Return ds.Tables(0).Rows(0).Item("Product_Event")
        Else
            Return ""
        End If
    End Function

    Public Function Get_SaleOrderForRelease(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from SAP_Sale_Order "
        sql &= " where Booking_no = @Booking_no AND Credit_Limit = 'N' "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no.Trim()
        adap.Fill(ds, "Product")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Payment_Bank(ByVal Booking_no As String, ByVal Customer As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Payment_Bank "
        sql &= " where Booking_no = @Booking_no AND Customer = @Customer"
        sql &= " Order By Tracking DESC"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no.Trim()
        adap.SelectCommand.Parameters.Add("@Customer", SqlDbType.NVarChar, 20).Value = Customer.Trim()
        adap.Fill(ds, "CustomerPayment")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function GetRowForSaleOrder(ByVal dt As DataTable)
        Dim dat As New DataTable
        dat.Columns.Add("sold_to", GetType(String))
        dat.AcceptChanges()
        Dim nameOld As String = "X"
        For Each drx As DataRow In dt.Rows
            If (nameOld <> drx.Item("Owner_Product")) Then
                Dim dr As DataRow = dat.NewRow()
                dr.Item("sold_to") = drx.Item("Owner_Product")
                dat.Rows.Add(dr)
                dat.AcceptChanges()
                nameOld = drx.Item("Owner_Product")
            End If
        Next

        Return dat
    End Function

    Public Function Get_MatFor_Sale_Order(ByVal Booking_no As String, ByVal Owner_Product As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select Temp_Estimate.*,Meterial.Division,Meterial.TAXCLASS,Meterial.MAT_WHT,Meterial.BASE_UOM "
        sql &= " From Temp_Estimate,Meterial "
        sql &= " WHERE Booking_no = @Booking_no "
        If (Owner_Product <> "") Then
            sql &= " AND Temp_Estimate.Owner_Product = @Owner_Product "
        End If
        sql &= " AND Temp_Estimate.Material = Meterial.Material  "
        sql &= " AND Temp_Estimate.data4 <> '0.00' "
        sql &= " ORDER BY  Temp_Estimate.Owner_Product,Meterial.Division,Temp_Estimate.Material "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no.Trim()
        If (Owner_Product <> "") Then
            adap.SelectCommand.Parameters.Add("@Owner_Product", SqlDbType.NVarChar, 10).Value = Owner_Product.Trim()
        End If
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_OtherServiceRecord() As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * From OtherService_Head "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        '  adap.SelectCommand.Parameters.Add("@Group_no", SqlDbType.NVarChar, 200).Value = Group

        adap.Fill(ds, "OtherService")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_ProductGroupMaster(ByVal Group As String, ByVal Port As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * From Product_Group where Group_no <> '' "
        If (Group <> "") Then
            sql &= " and ((Group_no Like '%' + @Group_no + '%') OR (Group_name Like '%' + @Group_no + '%'))"
        End If
        If (Port <> "A") Then
            sql &= " and (Group_PORT = @Group_PORT) "
        End If
        sql &= " ORDER By Group_name Desc"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        If (Group <> "") Then
            adap.SelectCommand.Parameters.Add("@Group_no", SqlDbType.NVarChar, 200).Value = Group
        End If
        If (Port <> "A") Then
            adap.SelectCommand.Parameters.Add("@Group_PORT", SqlDbType.NVarChar, 1).Value = Port
        End If
        adap.Fill(ds, "Product_group")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function


    Public Function Get_Buyer_Seller(ByVal BS As String, ByVal BS_Group As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * From Buyer_Seller where BS_no <> '' "
        If (BS <> "") Then
            sql &= " and ((BS_no Like '%' + @BS_no + '%') OR (BS_name Like '%' + @BS_no + '%'))"
        End If
        If (BS_Group <> "A") Then
            sql &= " and (BS_Group = @BS_Group) "
        End If
        sql &= " ORDER By BS_no Desc"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        If (BS <> "") Then
            adap.SelectCommand.Parameters.Add("@BS_no", SqlDbType.NVarChar, 200).Value = BS
        End If
        If (BS_Group <> "A") Then
            adap.SelectCommand.Parameters.Add("@BS_Group", SqlDbType.NVarChar, 1).Value = BS_Group
        End If
        adap.Fill(ds, "BS")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Product_no(ByVal Product_name As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Product,Product_group "
        sql &= " where Product_name = @Product_name "
        sql &= " and Product.Group_no = Product_group.Group_no "
        ' sql &= " and Group_no = @Group_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Product_name", SqlDbType.NVarChar, 200).Value = Product_name.Trim()
        ' adap.SelectCommand.Parameters.Add("@Group_no", SqlDbType.NVarChar, 10).Value = Group_no
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_FacilityCode(ByVal Mla_Host_Diameter As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Facility "
        sql &= " where Mla_Host + '-' + Diameter = @Facility "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Facility", SqlDbType.NVarChar, 800).Value = Mla_Host_Diameter.Trim()
        adap.Fill(ds, "Facility")
        adap = Nothing
        con.Close()
        If (ds.Tables(0).Rows.Count = 1) Then
            Return ds.Tables(0).Rows(0).Item("Facility_Code") & ""
        Else
            Return ""
        End If
    End Function

    Public Function Get_Facility(ByVal Mla_Host As String, ByVal Diameter As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Facility "
        sql &= " where Facility_Code <> '' "
        If (Mla_Host <> "") Then
            sql &= " and ((Facility_Code Like '%' + @Facility + '%') OR (Mla_Host Like '%' + @Facility + '%'))"
        End If
        If (Diameter <> "") Then
            sql &= " and (Diameter Like '%' + @Diameter + '%' ) "
        End If
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Facility", SqlDbType.NVarChar, 400).Value = Mla_Host
        adap.SelectCommand.Parameters.Add("@Diameter", SqlDbType.NVarChar, 400).Value = Diameter
        adap.Fill(ds, "Facility")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Shore(ByVal Shore As String, ByVal PORT As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Shore "
        sql &= " where Shore_Code <> '' "
        If (Shore <> "") Then
            sql &= " and ((Shore_Code Like '%' + @Shore + '%') OR (Shore_Name Like '%' + @Shore + '%'))"
        End If
        If (PORT <> "") Then
            sql &= " and PORT = @PORT "
        End If
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Shore", SqlDbType.NVarChar, 500).Value = Shore
        adap.SelectCommand.Parameters.Add("@PORT", SqlDbType.NVarChar, 1).Value = PORT
        adap.Fill(ds, "Shore")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_ShoreForProduct(ByVal Product_Group As String, ByVal BookingWharf As String, ByVal L_D As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select S.* from Shore S,Loading_Unloading U,Loading_Facility F "
        sql &= " where U.Group_Code = @Group_Code "
        sql &= " and U.Shore_Code = S.Shore_Code "
        sql &= " and U.Load_Code = F.Load_Code  "
        sql &= " and F.BookingWharf = @BookingWharf  "
        sql &= " and F.L_D = @L_D  "

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Group_Code", SqlDbType.NVarChar, 10).Value = Product_Group.Trim()
        adap.SelectCommand.Parameters.Add("@BookingWharf", SqlDbType.NVarChar, 5).Value = BookingWharf.Trim()
        adap.SelectCommand.Parameters.Add("@L_D", SqlDbType.NVarChar, 1).Value = L_D
        adap.Fill(ds, "Shore")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_FacilityForProduct(ByVal Shore As String, ByVal Line As String, ByVal Group_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select Distinct F.Mla_Host + '-' + F.Diameter as Facility_Name,F.Facility_Code from Loading_Unloading U,Loading_Facility L,Facility F "
        sql &= " where U.Group_Code = @Group_no "
        sql &= " AND  U.Shore_Code = @Shore_Code    "
        sql &= " And U.Line_Code = @Line "
        sql &= " And U.Load_Code = L.Load_Code "
        sql &= " And L.Facility_Code = F.Facility_Code "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Shore_Code", SqlDbType.NVarChar, 10).Value = Shore
        adap.SelectCommand.Parameters.Add("@Line", SqlDbType.NVarChar, 10).Value = Line
        adap.SelectCommand.Parameters.Add("@Group_no", SqlDbType.NVarChar, 10).Value = Group_no
        adap.Fill(ds, "Line")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Slop(ByVal slop_code As String, ByVal Slop As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Slop_Master "
        sql &= " where Slop_Code <> '' "
        If (slop_code <> "") Then
            sql &= " and Slop_Code = @Slop_Code"
        End If
        If (Slop <> "") Then
            sql &= " and (Slop_Name Like '%' + @Slop + '%')"
        End If
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Slop", SqlDbType.NVarChar, 200).Value = Slop.Trim()
        adap.SelectCommand.Parameters.Add("@Slop_Code", SqlDbType.NVarChar, 10).Value = slop_code.Trim()
        adap.Fill(ds, "Slop")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_SlopForDate(ByVal _Date As String, ByVal Slop_Code As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Slop_Transection "
        sql &= " where Slop_Code <> '' "
        If (Slop_Code <> "") Then
            sql &= " and Slop_Code = @Slop_Code"
        End If
        If (_Date <> "") Then
            sql &= " and (Slop_Date = @Slop_Date)"
        End If

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Slop_Date", SqlDbType.NVarChar, 15).Value = _Date
        adap.SelectCommand.Parameters.Add("@Slop_Code", SqlDbType.NVarChar, 10).Value = Slop_Code.Trim()
        adap.Fill(ds, "Slop")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Line(ByVal Shore As String, ByVal Line As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select Line.*,Shore.Shore_Name from Line,Shore "
        sql &= " where Line.Line_Code <> '' "
        If (Shore <> "") Then
            sql &= " AND Line.Shore_Code = @Shore_Code "
        End If
        If (Line <> "") Then
            sql &= " and ((Line_Code Like '%' + @Line + '%') OR (Line_Name Like '%' + @Line + '%'))"
        End If
        sql &= " AND Line.Shore_Code = Shore.Shore_Code "
        sql &= " ORDER BY Shore_Code "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Shore_Code", SqlDbType.NVarChar, 10).Value = Shore.Trim()
        adap.SelectCommand.Parameters.Add("@Line", SqlDbType.NVarChar, 500).Value = Line
        adap.Fill(ds, "Line")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_LineForProduct(ByVal Shore As String, ByVal Group_Code As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select L.* from Loading_Unloading U,Line L "
        sql &= " where U.Group_Code = @Group_Code "
        sql &= " and U.Shore_Code = @Shore_Code "
        sql &= " and U.Line_Code = L.Line_Code "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Shore_Code", SqlDbType.NVarChar, 10).Value = Shore.Trim()
        adap.SelectCommand.Parameters.Add("@Group_Code", SqlDbType.NVarChar, 10).Value = Group_Code.Trim()
        adap.Fill(ds, "Line")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_GL(ByVal Bank_Code As String, ByVal GL As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select Gl_Account.*,Bank.Bank_Name from Bank,Gl_Account "
        sql &= " where Bank.Bank_Code <> '' "
        If (Bank_Code <> "") Then
            sql &= " AND Bank.Bank_SAP = @Bank_Code "
        End If
        If (GL <> "") Then
            sql &= " and ((Gl_Account Like '%' + @GL + '%') OR (Gl_Name Like '%' + @GL + '%'))"
        End If
        sql &= " AND Bank.Bank_SAP = Gl_Account.Bank_Code "
        sql &= " ORDER BY Bank_Code "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Bank_Code", SqlDbType.NVarChar, 10).Value = Bank_Code.Trim()
        adap.SelectCommand.Parameters.Add("@GL", SqlDbType.NVarChar, 200).Value = GL
        adap.Fill(ds, "Line")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_HolidayALl(ByVal Day As String, ByVal Month As String, ByVal Year As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Holiday "
        sql &= " where Day <> '' "
        If (Day <> "") Then
            sql &= " AND Day = @Day "
        End If
        If (Month <> "") Then
            sql &= " and Month = @Month"
        End If
        If (Year <> "") Then
            sql &= " and Year = @Year "
        End If
        sql &= " ORDER BY Year,Month,Day Desc "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Day", SqlDbType.NVarChar, 2).Value = Day
        adap.SelectCommand.Parameters.Add("@Month", SqlDbType.NVarChar, 2).Value = Month
        adap.SelectCommand.Parameters.Add("@Year", SqlDbType.NVarChar, 4).Value = Year
        adap.Fill(ds, "Holiday")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Bank(ByVal Bank As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Bank "
        sql &= " where Bank.Bank_Code <> '' "
        If (Bank <> "") Then
            sql &= " and ((Bank_SAP Like '%' + @Bank + '%') OR (Bank_Name Like '%' + @Bank + '%'))"
        End If
        sql &= " ORDER BY Bank_Code "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Bank", SqlDbType.NVarChar, 200).Value = Bank
        adap.Fill(ds, "Bank")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Color(ByVal Color As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Color "
        sql &= " where Color_Code <> '' "
        If (Color <> "") Then
            sql &= " and (Color_Name Like '%' + @Color + '%') "
        End If
        sql &= " ORDER BY Color_Code "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Color", SqlDbType.NVarChar, 200).Value = Color
        adap.Fill(ds, "Color")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_TugMaster(ByVal Tug As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Tug "
        sql &= " where Tug_Code <> '' "
        If (Tug <> "") Then
            sql &= " and Tug_Name Like '%' + @Tug + '%'"
        End If
        sql &= " ORDER BY Tug_Name "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Tug", SqlDbType.NVarChar, 200).Value = Tug
        adap.Fill(ds, "Tug")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_StockMaster(ByVal Grade As String, ByVal Brand As String, ByVal Stock_Code As String, ByVal Stock_Type As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Stock "
        sql &= " where Stock_Code <> '' "
        If (Grade <> "") Then
            sql &= " and Grade Like '%' + @Grade + '%'"
        End If
        If (Brand <> "") Then
            sql &= " and Brand Like '%' + @Brand + '%'"
        End If
        If (Stock_Code <> "") Then
            sql &= " and Stock_Code = @Stock_Code "
        End If
        If (Stock_Type <> "") Then
            sql &= " and Type_Additional in (" & Stock_Type & ") "
        End If
        sql &= " ORDER BY Stock_Code "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Grade", SqlDbType.NVarChar, 200).Value = Grade
        adap.SelectCommand.Parameters.Add("@Brand", SqlDbType.NVarChar, 200).Value = Brand
        adap.SelectCommand.Parameters.Add("@Stock_Code", SqlDbType.NVarChar, 10).Value = Stock_Code
        adap.Fill(ds, "Stock_Code")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Marker_Report_Point(ByVal stock_Code As String, ByVal _D As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Marker_Report_Point "
        sql &= " where Marker_Code <> '' "
        If (stock_Code <> "") Then
            sql &= " and Marker_Code = @Marker_Code "
        End If
        If (_D) Then
            sql &= " and Date_Marker = @Date_Marker "
        End If
        sql &= " ORDER BY Marker_Code "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Marker_Code", SqlDbType.NVarChar, 10).Value = stock_Code
        adap.SelectCommand.Parameters.Add("@Date_Marker", SqlDbType.NVarChar, 8).Value = _D
        adap.Fill(ds, "Date_Marker")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_StockActionToCurrent(ByVal stock_Code As String, ByVal _D As String, ByVal _To As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Stock_Add_Remove "
        sql &= " where Stock_Code <> '' "
        If (stock_Code <> "") Then
            sql &= " and Stock_Code = @Stock_Code "
        End If
        If (_D) Then
            sql &= " and Stock_Date between " & _D & " and " & _To
        End If
        sql &= " ORDER BY Stock_Code "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Stock_Code", SqlDbType.NVarChar, 10).Value = stock_Code
        adap.Fill(ds, "Stock_Code_Current")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function CHeck_StockDuplicate(ByVal stock_code As String, ByVal Type_Additional As String, ByVal Product_no As String, ByVal color As String) As Integer
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from stock ,Stock_Relation"
        sql &= " where stock.Type_Additional = @Type_Additional  "
        sql &= " and stock.Stock_Code = stock_Relation.Stock_code "
        sql &= " and stock_Relation.Product_no  = @Product_no "
        If (color <> "") Then
            sql &= " and color Not in ('" & color & "') "
        End If
        If (stock_code <> "") Then
            sql &= " and Stock.Stock_Code Not in ('" & stock_code & "') "
        End If
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Type_Additional", SqlDbType.NVarChar, 1).Value = Type_Additional
        adap.SelectCommand.Parameters.Add("@Product_no", SqlDbType.NVarChar, 10).Value = Product_no
        adap.Fill(ds, "Check_Dulipicate")
        adap = Nothing
        con.Close()
        Return ds.Tables(0).Rows.Count
    End Function

    Public Function Check_ForUSE_Stock(ByVal Product_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from stock ,Stock_Relation"
        sql &= " where stock.Active = 'Y'  "
        sql &= " and stock.stock_Code = stock_Relation.Stock_Code "
        sql &= " and stock_Relation.Product_no  = @Product_no "

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Product_no", SqlDbType.NVarChar, 10).Value = Product_no
        adap.Fill(ds, "Check_USE")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Stock_Relation(ByVal Stock_Code As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Stock_Relation "
        sql &= " where Stock_Code <> '' "
        If (Stock_Code <> "") Then
            sql &= " and Stock_Code = @Stock_Code "
        End If
        sql &= " ORDER BY Stock_Code "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Stock_Code", SqlDbType.NVarChar, 10).Value = Stock_Code
        adap.Fill(ds, "Stock_Code")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Booking_Bas_Vessel(ByVal BV_no As String, ByVal Company As String, ByVal Product_name As String, ByVal Wharf As String, ByVal Fender As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  Select B.*,'B' + B.BV_no As Bv_Show,W.Wharf as WharfShow from Booking_Bas_Vessel B,Wharf W "
        sql &= " where B.Wharf = W.BookingWharf "
        If (BV_no <> "") Then
            sql &= " and B.BV_no = @BV_no "
        End If
        If (Company <> "") Then
            sql &= " and B.Company Like '%' + @Company + '%'"
        End If
        If (Product_name <> "") Then
            sql &= " and B.Product_Name Like '%' + @Product_Name + '%'"
        End If
        If (Wharf <> "0") Then
            sql &= " and B.Wharf = @Wharf "
        End If
        If (Fender <> "") Then
            sql &= " and B.Fender Like '%' + @Fender + '%'"
        End If
        sql &= " ORDER BY BV_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@BV_no", SqlDbType.NVarChar, 10).Value = BV_no
        adap.SelectCommand.Parameters.Add("@Company", SqlDbType.NVarChar, 2000).Value = Company
        adap.SelectCommand.Parameters.Add("@Product_Name", SqlDbType.NVarChar, 200).Value = Product_name
        adap.SelectCommand.Parameters.Add("@Wharf", SqlDbType.NVarChar, 5).Value = Wharf
        adap.SelectCommand.Parameters.Add("@Fender", SqlDbType.NVarChar, 50).Value = Fender
        adap.Fill(ds, "Booking_Bas_Vessel")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_BasMaster(ByVal Bas As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Bas_Master "
        sql &= " where Bas_no <> '' "
        If (Bas <> "") Then
            sql &= " and Bas_name Like '%' + @Bas + '%'"
        End If
        sql &= " ORDER BY Bas_name "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Bas", SqlDbType.NVarChar, 500).Value = Bas
        adap.Fill(ds, "Bas")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Cause_OF_Delay(ByVal Cause_OF_Delay As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from CauseOfDelay "
        sql &= " where Cause_Code <> '' "
        If (Cause_OF_Delay <> "") Then
            sql &= " and Cause_Delay Like '%' + @Cause + '%'"
        End If
        sql &= " ORDER BY Cause_Delay "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Cause", SqlDbType.NVarChar, 500).Value = Cause_OF_Delay
        adap.Fill(ds, "Cause")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_LoadingUnloadingByOne(ByVal Load_Code As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * "
        sql &= "  from Loading_Unloading"
        sql &= "  where Load_Code = @Load_Code "

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Load_Code", SqlDbType.NVarChar, 10).Value = Load_Code
        adap.Fill(ds, "Loading")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_LoadingUnloading(ByVal Shore As String, ByVal Line As String, ByVal Product As String, ByVal Wharf As String, ByVal Facility As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * "
        sql &= "  from Loading_Unloading L,Loading_Facility LF,Facility F,Wharf W,Shore S,Line LI,Product_Group G "
        sql &= "  where L.Load_Code = LF.Load_Code "
        If (Facility <> "") Then
            sql &= " and (F.Mla_Host + F.Diameter) Like '%' + @Facility + '%' "
        End If
        sql &= "  and LF.Facility_Code = F.Facility_Code "
        If (Wharf <> "0") Then
            sql &= " and LF.BookingWharf = @Wharf "
        End If
        sql &= " and LF.BookingWharf = W.BookingWharf "
        If (Shore <> "0") Then
            sql &= " and L.Shore_Code = @Shore_Code "
        End If
        sql &= " and L.Shore_Code = S.Shore_Code "
        If (Line <> "0") Then
            sql &= " and L.Line_Code = @Line_Code "
        End If
        sql &= " and L.Line_Code = LI.Line_Code "
        If (Product <> "0") Then
            sql &= " and L.Group_Code = @Group "
        End If
        sql &= " and L.Group_Code = G.Group_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Shore_Code", SqlDbType.NVarChar, 10).Value = Shore
        adap.SelectCommand.Parameters.Add("@Line_Code", SqlDbType.NVarChar, 10).Value = Line
        adap.SelectCommand.Parameters.Add("@Wharf", SqlDbType.NVarChar, 5).Value = Wharf
        adap.SelectCommand.Parameters.Add("@Facility", SqlDbType.NVarChar, 200).Value = Facility
        adap.SelectCommand.Parameters.Add("@Group", SqlDbType.NVarChar, 10).Value = Product
        adap.Fill(ds, "Loading")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Loding_Facility(ByVal Load_Code As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select *,F.Mla_Host + '-' + F.Diameter as Facility "
        sql &= "  from Loading_Facility L,Facility F,Wharf W "
        sql &= "  where L.Load_Code = @Load_Code "
        sql &= " AND L.Facility_Code = F.Facility_Code "
        sql &= " AND L.BookingWharf = W.BookingWharf "

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Load_Code", SqlDbType.NVarChar, 10).Value = Load_Code
        adap.Fill(ds, "Loding_Facility")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Loding_Wharf(ByVal Load_Code As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * "
        sql &= "  from Loading_Wharf L,Wharf W "
        sql &= "  where L.Load_Code = @Load_Code "
        sql &= " and L.BookingWharf = W.BookingWharf "

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Load_Code", SqlDbType.NVarChar, 10).Value = Load_Code
        adap.Fill(ds, "Loding_Facility")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Group_no(ByVal Group_name As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Product_Group "
        sql &= " where Group_name = @Group_name "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Group_name", SqlDbType.NVarChar, 200).Value = Group_name
        adap.Fill(ds, "Group")
        adap = Nothing
        con.Close()
        If (ds.Tables(0).Rows.Count = 1) Then
            Return ds.Tables(0).Rows(0).Item("Group_no")
        Else
            Return ""
        End If
    End Function

    Public Function Get_IportNumber() As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select Top 1 * from Iport_Number "
        sql &= " WHERE Call_Date = '" & Date.Now.ToString("yyyyMMdd", New Globalization.CultureInfo("en-US")) & "' "
        sql &= " ORDER BY Number DESC "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.Fill(ds, "Iport_Number")
        adap = Nothing
        con.Close()
        If (ds.Tables(0).Rows.Count > 0) Then
            Return ds.Tables(0).Rows(0).Item("Call_Date") & "-" & (CInt(ds.Tables(0).Rows(0).Item("Number")) + 1).ToString("000")
        Else
            Return Date.Now.ToString("yyyyMMdd", New Globalization.CultureInfo("en-US")) & "-001"
        End If
    End Function

    Public Function Get_IportNumber_IsCancel(ByVal IportNo As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT TOP(1) * FROM SAP_IPORT_DOC "
        sql &= "WHERE IPORTNO = @IPORTNO "
        sql &= "ORDER BY IPORTNO DESC"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@IPORTNO", SqlDbType.NVarChar, 10).Value = IportNo.ToString()
        adap.Fill(ds, "Iport_Number")
        adap = Nothing
        con.Close()
        If (ds.Tables(0).Rows.Count > 0) Then
            Return ds.Tables(0).Rows(0).Item("IPORTNO")
        Else
            Return ""
        End If
    End Function

    Public Function Get_BELNR_IsCancel(ByVal BELNR As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT TOP(1) * FROM SAP_IPORT_DOC "
        sql &= "WHERE BELNR = @BELNR "
        sql &= "ORDER BY IPORTNO DESC"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@BELNR", SqlDbType.NVarChar, 10).Value = BELNR.ToString()
        adap.Fill(ds, "Iport_Number")
        adap = Nothing
        con.Close()
        If (ds.Tables(0).Rows.Count > 0) Then
            Return ds.Tables(0).Rows(0).Item("BELNR")
        Else
            Return ""
        End If
    End Function

    Public Function Get_BILLNO_IsCancel(ByVal BookingNo As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet

        'sql = "SELECT BILL_NO AS BILL_NO, Booking_No, 'Y' AS isCancel FROM SAP_IPORT_DOC "
        'sql &= "WHERE Booking_No = @BookingNo AND ISNULL(BILL_NO, '') <> '' AND ISNULL(isActive, 'N') = 'Y' "
        'sql &= "UNION" & vbCrLf
        'sql &= "SELECT EX_BILL_NO AS BILL_NO, Booking_no, 'N' AS isCancel FROM SAP_Sale_Order" & vbCrLf
        'sql &= "WHERE BOOKING_no = @BookingNo AND ISNULL(EX_BILL_NO, '') <> ''" & vbCrLf
        'sql &= "ORDER BY BILL_NO ASC"

        sql = "SELECT     IPORTNO, BILL_NO, Booking_No, (CASE ISNULL" & vbCrLf
        sql &= "                          ((SELECT     EX_BILL_NO" & vbCrLf
        sql &= "                              FROM         SAP_Sale_Order" & vbCrLf
        sql &= "                              WHERE     EX_BILL_NO = BILL_NO AND Iport_no = IPORTNO), '') " & vbCrLf
        sql &= "                              WHEN '' THEN 'N' " & vbCrLf
        sql &= "                              WHEN 'Y' THEN isActive" & vbCrLf
        sql &= "                              ELSE 'P' END) AS isActive" & vbCrLf
        sql &= "FROM         dbo.SAP_IPORT_DOC" & vbCrLf
        sql &= "WHERE     (Booking_No = @BookingNo) AND (ISNULL(BILL_NO, N'') <> '') AND (ISNULL(isActive, N'N') = 'Y')" & vbCrLf
        sql &= "UNION" & vbCrLf
        sql &= "SELECT IPORT_NO, EX_BILL_NO AS BILL_NO, Booking_no, (CASE ISNULL" & vbCrLf
        sql &= "                          ((SELECT     BILL_NO" & vbCrLf
        sql &= "                              FROM         SAP_IPORT_DOC" & vbCrLf
        sql &= "                              WHERE     BILL_NO = S.EX_BILL_NO AND IPORTNO = S.Iport_no AND (ISNULL(isActive, N'N') = 'Y')), '') " & vbCrLf
        sql &= "                              WHEN '' THEN 'Y' " & vbCrLf
        sql &= "                              WHEN 'N' THEN 'N'" & vbCrLf
        sql &= "                              WHEN 'Y' THEN 'Y'" & vbCrLf
        sql &= "                              ELSE 'P' END) AS isActive " & vbCrLf
        sql &= "FROM SAP_Sale_Order S" & vbCrLf
        sql &= "WHERE BOOKING_no = @BookingNo AND ISNULL(EX_BILL_NO, '') <> ''" & vbCrLf
        sql &= "ORDER BY BILL_NO ASC"

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@BookingNo", SqlDbType.NVarChar, 10).Value = BookingNo.ToString()
        adap.Fill(ds, "Iport_Number")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
        'If (ds.Tables(0).Rows.Count > 0) Then
        '    Return ds.Tables(0).Rows(0).Item("BILL_NO")
        'Else
        '    Return ""
        'End If
    End Function

    Public Function Get_WharfAll(ByVal PORT As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Wharf "
        sql &= " where PORT = @PORT "
        sql &= " ORDER By Wharf "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@PORT", SqlDbType.NVarChar, 1).Value = PORT
        adap.Fill(ds, "PORT")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_WharfName(ByVal BookingWharf As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select Wharf from Wharf "
        sql &= " where BookingWharf = @BookingWharf "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@BookingWharf", SqlDbType.NVarChar, 5).Value = BookingWharf
        adap.Fill(ds, "PORT")
        adap = Nothing
        con.Close()
        If (ds.Tables(0).Rows.Count > 0) Then
            Return ds.Tables(0).Rows(0).Item("Wharf") & ""
        Else
            Return ""
        End If

    End Function

    Public Function Get_MaintenanceWharf(ByVal PORT As String, ByVal Wharf As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select BookingWharf.*,Wharf.Wharf,Wharf.PORT from "
        sql &= " BookingWharf,Wharf "
        sql &= " where Booking_no Like 'M%' "
        If (Wharf <> "0") Then
            sql &= " and BookingWharf.BookingWharf = @Wharf "
        End If
        sql &= " and BookingWharf.BookingWharf = Wharf.BookingWharf "
        If (PORT <> "") Then
            sql &= " and Wharf.PORT = @PORT "
        End If
        sql &= " Order By Substring(BookingWharf.DateStart,7,4) + Substring(BookingWharf.DateStart,4,2) + Substring(BookingWharf.DateStart,1,2) DESC ,Substring(BookingWharf.DateEnd,7,4) + Substring(BookingWharf.DateEnd,4,2) + Substring(BookingWharf.DateEnd,1,2) DESC "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        If (PORT <> "") Then
            adap.SelectCommand.Parameters.Add("@PORT", SqlDbType.NVarChar, 1).Value = PORT
        End If
        If (Wharf <> "0") Then
            adap.SelectCommand.Parameters.Add("@Wharf", SqlDbType.NVarChar, 5).Value = Wharf
        End If

        adap.Fill(ds, "PORT")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Booking_LogRemarkVPS(ByVal Booking_no As String, ByVal RoleWant As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String = ""
        Dim ds As New DataSet
        If (RoleWant = "VPS") Then
            sql = "select Booking_log,Remark_VPS "
            sql &= " from Booking_Log"
            sql &= " where Booking_no = @Booking_no"
            sql &= " and  Status in ('VC','VP','VA')  "
        ElseIf (RoleWant = "MRI") Then
            sql = "select Booking_log,Remark_MRI "
            sql &= " from Booking_Log"
            sql &= " where Booking_no = @Booking_no"
            sql &= " and  Status in ('MC','MP','MA','MO')  "
        End If
        sql &= " order by Booking_log asc "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Booking_Product(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT *,case Product_Index + '' when 1 then '1st' when 2 then '2nd' when 3 then '3rd' when 4 then Product_Index +'th' else Product_Index end as indexproduct "
        sql &= " FROM Booking_Product"
        sql &= " WHERE Booking_no = @Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Dim dtProduct As DataTable = ds.Tables(0)

        Return dtProduct
    End Function

    Public Function Get_Booking_Product(ByVal Booking_no As String, ByVal Owner As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT *,case Product_Index + '' when 1 then '1st' when 2 then '2nd' when 3 then '3rd' when 4 then Product_Index +'th'" _
            & " else Product_Index end as indexproduct "
        sql &= " FROM Booking_Product"
        sql &= " WHERE Booking_no = @Booking_no "
        If (Owner <> "") Then
            sql &= " AND Owner_Product_no = @Owner_Product_no "
        End If
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        If (Owner <> "") Then
            adap.SelectCommand.Parameters.Add("@Owner_Product_no", SqlDbType.NVarChar, 10).Value = Owner
        End If
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Booking_Gl_Account(ByVal Booking_no As String, ByVal Customer As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String = ""
        Dim ds As New DataSet
        sql += "SELECT DISTINCT TOP (100) PERCENT S.Value_Date, S.Amount, G.Gl_Name, S.Iport_no, S.Ex_Belnr, G.Bank_Code, B.Bank_SAP, B.Bank_Name "
        sql += "FROM         dbo.SAP_Advanced_Payment AS S INNER JOIN "
        sql += "                      dbo.Gl_Account AS G ON S.Gl_Account = G.Gl_account INNER JOIN "
        sql += "                      dbo.SAP_Advanced_Payment_Material AS M ON S.Iport_no = M.Iport_no LEFT OUTER JOIN "
        sql += "                      dbo.Bank AS B ON G.Bank_Code = B.Bank_SAP "
        sql &= "WHERE S.Booking_no = @Booking_no "



        sql &= "AND M.Customer = @Customer "
        sql += "ORDER BY S.Value_Date DESC, S.Ex_Belnr DESC"

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        Try
            Customer = CInt(Customer)
        Catch ex As Exception

        End Try
        adap.SelectCommand.Parameters.Add("@Customer", SqlDbType.NVarChar, 10).Value = Customer
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Load_Unload_Product_PTOL(ByVal Booking_no As String, ByVal Product_no As String, ByVal BP_Code As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT Load_Unload_Product.*,Product.Product_name "
        sql &= " FROM Load_Unload_Product,Product"
        sql &= " WHERE Booking_no = @Booking_no "
        Try
            If (Product_no <> "") Then
                Dim pn As Integer = CInt(Product_no)
                sql &= " AND Load_Unload_Product.Product_no = @Product_no "
            End If
            sql &= " AND Load_Unload_Product.Product_no = Product.Product_no "
        Catch ex As Exception
            sql = "SELECT *,Product_no as Product_name "
            sql &= " FROM Load_Unload_Product"
            sql &= " WHERE Booking_no = @Booking_no "
            If (Product_no <> "") Then
                sql &= " AND Load_Unload_Product.Product_no = @Product_no "
            End If
        End Try
        If (BP_Code <> "") Then
            sql &= "AND Load_Unload_Product.BP_Code = @BP_Code " & vbCrLf
        End If
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.SelectCommand.Parameters.Add("@Product_no", SqlDbType.NVarChar, 10).Value = Product_no
        adap.SelectCommand.Parameters.Add("@BP_Code", SqlDbType.NVarChar, 25).Value = BP_Code
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Load_Unload_Product(ByVal Booking_no As String, ByVal Product_no As String, Optional ByVal BP_Code As String = "") As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT Load_Unload_Product.*,Product.Product_name "
        sql &= " FROM Load_Unload_Product,Product"
        sql &= " WHERE Booking_no = @Booking_no "
        Try
            If (Product_no <> "") Then
                Dim pn As Integer = CInt(Product_no)
                sql &= " AND Load_Unload_Product.Product_no = @Product_no "
            End If
            If (BP_Code <> "") Then
                sql &= " AND Load_Unload_Product.BP_Code = @BP_Code "
            End If
            sql &= " AND Load_Unload_Product.Product_no = Product.Product_no "
        Catch ex As Exception
            sql = "SELECT *,Product_no as Product_name "
            sql &= " FROM Load_Unload_Product"
            sql &= " WHERE Booking_no = @Booking_no "
            If (Product_no <> "") Then
                sql &= " AND Load_Unload_Product.Product_no = @Product_no "
            End If
        End Try

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.SelectCommand.Parameters.Add("@Product_no", SqlDbType.NVarChar, 10).Value = Product_no
        adap.SelectCommand.Parameters.Add("@BP_Code", SqlDbType.NVarChar, 25).Value = BP_Code
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Booking_Product_LogSheetOut(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT * "
        sql &= " FROM Booking_LogSheet_Out_Product"
        sql &= " WHERE Booking_no = @Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Booking_Product_BP(ByVal PORT As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT * "
        sql &= " FROM Booking_Product,Booking"
        sql &= " WHERE BP_Code + '' <> '' "
        sql &= " AND Booking_Product.Booking_no = Booking.Booking_no "
        sql &= " AND Booking.PORT = '" & PORT & "' "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.Fill(ds, "BP")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_CountVessel_Liquid(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT * "
        sql &= " FROM CountVessel_Liquid"
        sql &= " WHERE Booking_no = @Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "CountVessel_Liquid")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_CountVessel_LiquidNumber(ByVal Month As String, ByVal Year As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT Top 1 Number "
        sql &= " FROM CountVessel_Liquid"
        sql &= " WHERE Month = @Month "
        sql &= " AND Year = @Year "
        sql &= " ORDER By convert(int,Number) Desc "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Month", SqlDbType.NVarChar, 2).Value = Month
        adap.SelectCommand.Parameters.Add("@Year", SqlDbType.NVarChar, 4).Value = Year
        adap.Fill(ds, "CountVessel_LiquidNumber")
        adap = Nothing
        con.Close()
        If (ds.Tables(0).Rows.Count > 0) Then
            Return (CInt(ds.Tables(0).Rows(0).Item("Number")) + 1).ToString()
        Else
            Return "1"
        End If
    End Function

    Public Function Get_Load_Unload_Addition(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT * "
        sql &= " FROM Load_Unload_Addition"
        sql &= " WHERE Booking_no = @Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "Load_Unload_Addition")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Load_Unload_Addition_Stock(ByVal Booking_no As String) As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT *,'' as Remark "
        sql &= " FROM Load_Unload_Addition_Stock"
        sql &= " WHERE Booking_no = @Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "Load_Unload_Addition")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function Get_Load_Unload_Delay(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT *,Date_From + ' ' + Time_From + ' - ' + Date_To + ' ' + Time_To as Delay_DateTime "
        sql &= " FROM Load_Unload_Delay "
        sql &= " WHERE Booking_no = @Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "Load_Unload_Delay")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Load_Unload_Remark(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT *"
        sql &= " FROM Load_Unload_Remark "
        sql &= " WHERE Booking_no = @Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "Load_Unload_Remark")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Booking_ProductOperation(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  SELECT P.*,case P.Product_Index + '' when 1 then '1st' when 2 then '2nd' when 3 then '3rd' when 4 then P.Product_Index +'th' else P.Product_Index end as indexproduct,L.Line_Name,F.Mla_Host,F.Facility_Code,L.Line_Code,S.Shore_Code "
        sql &= "  FROM Booking_Product P,Line L,Facility F,Shore S "
        sql &= " WHERE Booking_no = @Booking_no "
        sql &= " and P.Product_Line = L.Line_Code "
        sql &= " and P.Product_Facility = F.Facility_Code "
        sql &= " and P.Product_Shore = S.Shore_Code "
        sql &= " ORDER BY P.Product_Index "

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Booking_ProductOperationBulk(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  SELECT P.*,case P.Product_Index + '' when 1 then '1st' when 2 then '2nd' when 3 then '3rd' when 4 then P.Product_Index +'th' else P.Product_Index end as indexproduct "
        sql &= "  FROM Booking_Product P "
        sql &= " WHERE Booking_no = @Booking_no "
        sql &= " ORDER BY Product_Index "

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Action_Operation(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  SELECT * "
        sql &= "  FROM Action_Operation "
        sql &= " WHERE Booking_no = @Booking_no "
        sql &= " ORDER BY A_Code "

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "Action_Operation")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_DefaultPayin(ByVal Customer_no As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT Default_Pay "
        sql &= " FROM Customer"
        sql &= " WHERE Customer_no = @Customer_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Customer_no", SqlDbType.NVarChar, 10).Value = Customer_no
        adap.Fill(ds, "Customer_no")
        adap = Nothing
        con.Close()
        If (ds.Tables(0).Rows.Count > 0) Then
            Return ds.Tables(0).Rows(0).Item("Default_Pay") & ""
        Else
            Return "0"
        End If
    End Function

    Public Function Get_BookingUpload(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT * "
        sql &= " FROM Booking_Upload"
        sql &= " WHERE Booking_no = @Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Load_Unload_Vessel(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT * "
        sql &= " FROM Load_Unload_Vessel"
        sql &= " WHERE Booking_no = @Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "Load_Unload_Vessel")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_ROWRATE_MAT(ByVal Meterial As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select LowRate "
        sql &= " from RelationMeterial"
        sql &= " where Meterial = @Meterial "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Meterial", SqlDbType.NVarChar, 18).Value = Meterial
        adap.Fill(ds, "MeterialLOWRATE")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Load_Unload_Vessel_Product_Detail(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select Booking_no,Product_name,LU_Connect_Date,LU_Connect_From,LU_Connect_To,LU_StartActual_Date,LU_StartActual_Time "
        sql &= " ,LU_StopActual_Date,LU_StopActual_Time,LU_Disconnect_Date,LU_Disconnect_From,LU_Disconnect_To "
        sql &= "  from Load_Unload_Product,Product "
        sql &= " WHERE Booking_no = @Booking_no "
        sql &= " and Load_Unload_Product.Product_no = Product.Product_no "
        sql &= " ORder by Substring(LU_Disconnect_Date,7,2)+Substring(LU_Disconnect_Date,4,2)+Substring(LU_Disconnect_Date,1,2),Substring(LU_Disconnect_To,1,2)+Substring(LU_Disconnect_To,4,2) "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "Load_Unload_Vessel")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Load_Unload_Vessel_Product_Detail_FOr_Status(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select Booking_no,LU_Connect_Date,LU_Connect_From,LU_Connect_To,LU_StartActual_Date,LU_StartActual_Time "
        sql &= " ,LU_StopActual_Date,LU_StopActual_Time,LU_Disconnect_Date,LU_Disconnect_From,LU_Disconnect_To "
        sql &= "  from Load_Unload_Product "
        sql &= " WHERE Booking_no = @Booking_no "
        sql &= " ORder by Substring(LU_Disconnect_Date,7,2)+Substring(LU_Disconnect_Date,4,2)+Substring(LU_Disconnect_Date,1,2),Substring(LU_Disconnect_To,1,2)+Substring(LU_Disconnect_To,4,2) "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "Load_Unload_Vessel")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Load_Unload_Vessel_Product_Detail_Bunker(ByVal Booking_no As String, ByVal Product_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select *  "
        sql &= "  from Load_Unload_Product "
        sql &= " WHERE Booking_no = @Booking_no "
        If (Product_no <> "") Then
            sql &= " AND Product_no = @Product_no "
        End If
        sql &= " ORder by Substring(LU_Disconnect_Date,7,2)+Substring(LU_Disconnect_Date,4,2)+Substring(LU_Disconnect_Date,1,2),Substring(LU_Disconnect_To,1,2)+Substring(LU_Disconnect_To,4,2) "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.SelectCommand.Parameters.Add("@Product_no", SqlDbType.NVarChar, 10).Value = Product_no
        adap.Fill(ds, "Load_Unload_Vessel")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_NameUser(ByVal User_Name As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT  Name "
        sql &= " FROM  UserIPORT"
        sql &= " Where User_name = @User_Name"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@User_Name", SqlDbType.NVarChar, 50).Value = User_Name
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        If (ds.Tables(0).Rows.Count = 1) Then
            Return ds.Tables(0).Rows(0).Item("Name")
        Else
            Return ""
        End If
    End Function

    Public Function Get_BookingTariff(ByVal Booking_no As String, ByVal Customer_no As String) As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT distinct MATERIAL,Booking_no,Text_line,Unit,Count,MAT_Default,KBETR,KONWA,MAT_Relation,MAT_Compute,MAT_Free,MAT_Compute as MAT_ComputeM "
        sql &= " FROM BookingTariff"
        sql &= " WHERE Booking_no = @Booking_no "
        If (Customer_no <> "") Then
            sql &= " AND Owner = @Owner "
            '  sql &= " AND Owner in ('','" & Customer_no & "') "
        End If
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        If (Customer_no <> "") Then
            If (Customer_no = "X") Then
                Customer_no = ""
            End If
            adap.SelectCommand.Parameters.Add("@Owner", SqlDbType.NVarChar, 10).Value = Customer_no
        End If
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function Get_BookingTariffOwner(ByVal Booking_no As String, ByVal Owner As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT distinct *,MAT_Compute as MAT_ComputeM "
        sql &= " FROM BookingTariff"
        sql &= " WHERE Booking_no = @Booking_no "
        sql &= " AND Owner = @Owner "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.SelectCommand.Parameters.Add("@Owner", SqlDbType.NVarChar, 10).Value = Owner
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Add_Tariff_New(ByVal Booking_no As String, ByVal Status As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT distinct *"
        sql &= " FROM Add_Tariff_New"
        sql &= " WHERE Booking_no = @Booking_no "
        sql &= " AND Status = @Status "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.SelectCommand.Parameters.Add("@Status", SqlDbType.NVarChar, 1).Value = Status
        adap.Fill(ds, "Add_Tariff_New")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_BookingTariffBulk(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT distinct Owner "
        sql &= " FROM BookingTariff"
        sql &= " WHERE Booking_no = @Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_BookingTariffForActual(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select Distinct B.MATERIAL,B.Booking_no,B.Text_line,B.Unit,B.Count,B.MAT_Default,B.KBETR,B.KONWA,B.MAT_Relation,B.MAT_Compute,B.MAT_Free " & vbCrLf
        sql &= "     ,T.data1, case Convert(Decimal(18,2),T.data2) * Convert(Decimal(18,2),T.data3) when T.data4 then T.data2 when '0' then T.data2 else T.data4 end as data2,case Convert(Decimal(18,2),T.data2) * Convert(Decimal(18,2),T.data3) when T.data4  then T.data3 When '0' then T.data1 else '1' end as data3,T.data4,'O' as New_Old " & vbCrLf
        sql &= "  from BookingTariff B,(select Booking_no,Material,MAT_NAME,data1,data2, case data3 when '' then '0' else data3 end as data3,data4,Owner_Product from Temp_Estimate) as T " & vbCrLf
        sql &= " where B.Booking_no = @Booking_no  " & vbCrLf
        sql &= " and B.Booking_no = T.Booking_no  " & vbCrLf
        sql &= "  and B.MATERIAL = T.MATERIAL " & vbCrLf
        sql &= " and T.data4 <> '0.00' " & vbCrLf
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "BookingTariff")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Customer(ByVal Customer As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT Distinct * "
        sql &= " FROM Customer"
        sql &= " WHERE Customer_no = @Customer_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Customer_no", SqlDbType.NVarChar, 10).Value = Customer
        adap.Fill(ds, "Booking")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_VesselFrom_Billing(ByVal EX_BILL_NO As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT Distinct * "
        sql &= " FROM SAP_Sale_Order "
        sql &= " WHERE EX_BILL_NO = @EX_BILL_NO "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@EX_BILL_NO", SqlDbType.NVarChar, 10).Value = EX_BILL_NO
        adap.Fill(ds, "EX_BILL_NO")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Billing_Advanct(ByVal Iport_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT EX_BILL_NO, ISNULL((SELECT TOP(1) BILL_NO FROM SAP_IPORT_DOC WHERE BILL_NO = SAP_Sale_Order.EX_BILL_NO), '') AS BILL_NO "
        sql &= "FROM SAP_Sale_Order "
        sql &= "WHERE Iport_no = @Iport_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Iport_no", SqlDbType.NVarChar, 12).Value = Iport_no
        adap.Fill(ds, "EX_BILL_NO")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_BillTo(ByVal Bill_to As String, ByVal Customer As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT Distinct * "
        sql &= " FROM Customer_Billto "
        sql &= " WHERE Bill_to = @Bill_to "
        sql &= " AND Customer_no = @Customer_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Bill_to", SqlDbType.NVarChar, 10).Value = Bill_to
        adap.SelectCommand.Parameters.Add("@Customer_no", SqlDbType.NVarChar, 10).Value = Customer
        adap.Fill(ds, "Bill_to")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_BillTo_For_Other(ByVal Customer As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " SELECT Bill_to,NAME1 + ' ' + NAME2 + ' ' + Name3 as Name From Customer_Billto "
        sql &= " WHERE Customer_no = @Customer "
        adap.SelectCommand = New System.Data.SqlClient.SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Customer", SqlDbType.NVarChar, 10).Value = Customer
        adap.Fill(ds, "CustomerBillTo")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

#Region "Logon Function"
    Public Function Get_Password(ByVal username As String) As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select UserIPORT.User_name,UserIPORT.Customer_no,UserIPORT.Customer_name,Name,Password,Email,Role.Role_no,Role.Role_name from UserIPORT,User_role,Role "
        sql &= " where UserIPORT.User_name = User_Role.User_name "
        sql &= " and User_Role.Role_no = Role.Role_no"
        sql &= " and UserIPORT.User_name = @User_name "
        sql &= " order by Role_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@User_name", SqlDbType.NVarChar, 50).Value = username
        adap.Fill(ds, "Logon")
        adap = Nothing
        con.Close()
        Return ds
    End Function
#End Region
    Public Function Get_VehicleMaster(ByVal name As String) As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT  Distinct Top 1000 * "
        sql &= " FROM Vessel"
        If (name <> "") Then
            sql &= " WHERE Vessel.VEH_TEXT Like '%' + @VEH_TEXT + '%'"
        End If
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@VEH_TEXT", SqlDbType.NVarChar, 10).Value = name
        adap.Fill(ds, "VEHICLE")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function Get_CustomerMaster(ByVal name As String) As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT Top 1000 *,NAME1 + ' ' + NAME2 + ' ' + Name3 as Name "
        sql &= " FROM Customer"
        If (name <> "") Then
            sql &= "  WHERE (((Name1 + Name2 + Name3) Like '%' + @Name + '%')OR(Customer_no Like '%' + @Name + '%'))"
        End If
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 20).Value = name
        adap.Fill(ds, "VEHICLE")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function Get_MeterailDefaultActual(ByVal Mat_Code As String, ByVal Plant As String) As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT Distinct Meterial.MATERIAL, case Meterial.Text_line when '' then Meterial.MATL_DESC Else Meterial.Text_line END as Text_line,"
        sql &= " SubMeterial.KONWA + '/' + SubMeterial.KMEIN + case isNull(subMeterial.unit,'') when '' then '' else '/' + subMeterial.unit end  as Unit , '1' as Count , Meterial.MAT_Default, Meterial.MAT_Relation,'Y' as MAT_ComputeM,SubMeterial.KBETR,SubMeterial.KONWA"
        sql &= " FROM Meterial,SubMeterial"

        sql &= " WHERE Meterial.MATERIAL Like '%' + @Mat_Code + '%' "

        sql &= "  AND Meterial.Material = SubMeterial.Material "
        sql &= " AND SubMeterial.WERKS = @WERKS "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Mat_Code", SqlDbType.NVarChar, 50).Value = Mat_Code
        adap.SelectCommand.Parameters.Add("@WERKS", SqlDbType.NVarChar, 4).Value = Plant
        adap.Fill(ds, "Default")
        adap = Nothing
        con.Close()
        Return DeleteMatDuplicate(ds)
    End Function

    Public Function Get_MeterailDefault(ByVal Mat_Code As String, ByVal MAT_Default As Boolean, ByVal NotIN As String, ByVal PORT As String, ByVal National As String, ByVal TypeBUY As String, ByVal Load As String, ByVal Disch As String, ByVal MaterailB As String, ByVal MAT_VesselNation As String, ByVal Plant As String) As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT Distinct Meterial.MATERIAL, case Meterial.Text_line when '' then Meterial.MATL_DESC Else Meterial.Text_line END as Text_line,"
        sql &= " SubMeterial.KONWA + '/' + SubMeterial.KMEIN + case isNull(subMeterial.unit,'') when '' then '' else '/' + subMeterial.unit end  as Unit , '1' as Count , Meterial.MAT_Default, Meterial.MAT_Relation,'Y' as MAT_ComputeM,SubMeterial.KBETR,SubMeterial.KONWA"
        sql &= " FROM Meterial,SubMeterial"
        If (MAT_Default) Then
            sql &= " WHERE  Meterial.MAT_Default = 'Y' "
            sql &= " AND Meterial.MAT_PORT In ('A','" & PORT & "') "
            'If (National = "O") Then
            '    sql &= " AND Meterial.MAT_National In ('A','" & National & "') "
            '    sql &= " AND Meterial.MAT_BUY In ('A','" & TypeBUY & "') "
            'Else
            '    sql &= " AND Meterial.MAT_National In ('A','" & National & "') "
            'End If
            'sql &= " AND Meterial.MAT_Product = 'A' "
            'sql &= " AND Meterial.MAT_VesselNation In ('A','" & MAT_VesselNation & "') "

            'If (Load = "Y") Then
            '    sql &= " AND Meterial.MAT_Load = 'Y' "
            'End If
            'If (Disch = "Y") Then
            '    sql &= " AND Meterial.MAT_Disch = 'Y' "
            'End If

        Else
            ' sql &= " WHERE  Meterial.MAT_Default Not in ('Y') "
            sql &= " WHERE Meterial.MATERIAL <> '' "
            If (Plant <> "") Then
                If (Plant = "1103") Then
                    Plant = "1104"
                    sql &= " AND  SubMeterial.WERKS <> @WERKS "
                ElseIf (Plant = "1104") Then
                    Plant = "1103"
                    sql &= " AND  SubMeterial.WERKS <> @WERKS "
                End If

            End If
        End If
        If Not (NotIN = "") Then
            sql &= " AND (Meterial.MATERIAL Not in (" & NotIN & ")) "
        End If
        If Not (Mat_Code = "") Then
            sql &= " AND ((Meterial.Text_line Like '%' + @Mat_Code + '%') OR (Meterial.MATL_DESC Like '%' + @Mat_Code + '%') OR (Meterial.MATERIAL Like '%' + @Mat_Code + '%')) "
        End If
        sql &= "  AND Meterial.Material = SubMeterial.Material "
        'If (MAT_Default) Then
        '    sql &= " UNION "

        '    sql &= "SELECT Distinct Meterial.MATERIAL, case Meterial.Text_line when '' then Meterial.MATL_DESC Else Meterial.Text_line END as Text_line,"
        '    sql &= " SubMeterial.KONWA + '/' + SubMeterial.KMEIN + case isNull(subMeterial.unit,'') when '' then '' else '/' + subMeterial.unit end  as Unit , '1' as Count , Meterial.MAT_Default, Meterial.MAT_Relation,'Y' as MAT_ComputeM,SubMeterial.KBETR,SubMeterial.KONWA"
        '    sql &= " FROM Meterial,SubMeterial"
        '    sql &= " WHERE  Meterial.MAT_Default = 'Y' "
        '    sql &= " AND Meterial.MAT_PORT In ('A','" & PORT & "') "
        '    If (National = "O") Then
        '        sql &= " AND Meterial.MAT_National In ('A','" & National & "') "
        '        sql &= " AND Meterial.MAT_BUY In ('A','" & TypeBUY & "') "
        '    Else
        '        sql &= " AND Meterial.MAT_National In ('A','" & National & "') "
        '    End If
        '    sql &= " AND Meterial.MAT_Product = 'B' "
        '    sql &= " AND Meterial.MAT_VesselNation In ('A','" & MAT_VesselNation & "') "
        '    sql &= " AND Meterial.MATERIAL in (''" & MaterailB & ") "
        '    sql &= "  AND Meterial.Material = SubMeterial.Material "

        '    'กรณี ยกเว้น บางสินค้า
        '    sql &= " UNION "

        '    sql &= "SELECT Distinct Meterial.MATERIAL, case Meterial.Text_line when '' then Meterial.MATL_DESC Else Meterial.Text_line END as Text_line,"
        '    sql &= " SubMeterial.KONWA + '/' + SubMeterial.KMEIN + case isNull(subMeterial.unit,'') when '' then '' else '/' + subMeterial.unit end  as Unit , '1' as Count , Meterial.MAT_Default, Meterial.MAT_Relation,'Y' as MAT_ComputeM,SubMeterial.KBETR,SubMeterial.KONWA"
        '    sql &= " FROM Meterial,SubMeterial"
        '    sql &= " WHERE  Meterial.MAT_Default = 'Y' "
        '    sql &= " AND Meterial.MAT_PORT In ('A','" & PORT & "') "
        '    If (National = "O") Then
        '        sql &= " AND Meterial.MAT_National In ('A','" & National & "') "
        '        sql &= " AND Meterial.MAT_BUY In ('A','" & TypeBUY & "') "
        '    Else
        '        sql &= " AND Meterial.MAT_National In ('A','" & National & "') "
        '    End If
        '    sql &= " AND Meterial.MAT_Product = 'C' "
        '    sql &= " AND Meterial.MAT_VesselNation In ('A','" & MAT_VesselNation & "') "
        '    sql &= " AND Meterial.MATERIAL Not in (''" & MaterailB & ") "
        '    sql &= "  AND Meterial.Material = SubMeterial.Material "
        'End If
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Mat_Code", SqlDbType.NVarChar, 50).Value = Mat_Code
        adap.SelectCommand.Parameters.Add("@WERKS", SqlDbType.NVarChar, 4).Value = Plant
        adap.Fill(ds, "Default")
        adap = Nothing
        con.Close()
        Return DeleteMatDuplicate(ds)
    End Function

    Public Function Get_Meterial_Shifting(ByVal Plant As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select * from Meterial,SubMeterial " & vbCrLf
        sql &= " where NOT(MAT_Shifting is null) " & vbCrLf
        sql &= " and MAT_Shifting <> '' " & vbCrLf
        sql &= " and MAT_Shifting > 0 " & vbCrLf
        sql &= " and Meterial.MATERIAL = SubMeterial.MATERIAL " & vbCrLf
        sql &= " And WERKS = @WERKS " & vbCrLf
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@WERKS", SqlDbType.NVarChar, 4).Value = Plant
        adap.Fill(ds, "SHIFTING")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Meterail_ProductMaterial(ByVal Product_no As String, ByVal Load As String, ByVal Disch As String, ByVal LoadIRPC As String, ByVal DischIRPC As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT * "
        sql &= " FROM Material_Product"
        sql &= " WHERE Product_no = @Product_no"
        If Load = "Y" Then
            sql &= " AND Product_Load = 'Y' "
        End If
        If Disch = "Y" Then
            sql &= " AND Product_Disch = 'Y' "
        End If
        If LoadIRPC = "Y" Then
            sql &= " AND Product_LoadIRPC = 'Y' "
        End If
        If DischIRPC = "Y" Then
            sql &= " AND Product_DischIRPC = 'Y' "
        End If
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Product_no", SqlDbType.NVarChar, 10).Value = Product_no
        adap.Fill(ds, "MAT_PRO")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Private Function DeleteMatDuplicate(ByVal ds As DataSet) As DataSet
        Dim MAT As String = ""
        Dim i As Integer = 0
        While (i < ds.Tables(0).Rows.Count)
            If (ds.Tables(0).Rows(i).Item("MATERIAL") = MAT) Then
                ds.Tables(0).Rows(i).Delete()
                ds.Tables(0).AcceptChanges()
                i = 0
                MAT = ""
            Else
                MAT = ds.Tables(0).Rows(i).Item("MATERIAL")
                i = i + 1
            End If
        End While
        Return ds
    End Function

    Public Function Get_MeterailMaster(ByVal code_name As String) As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT Distinct Top 1000  Meterial.MATERIAL, case Meterial.Text_line when '' then Meterial.MATL_DESC Else Meterial.Text_line END as Text_line,"
        sql &= " SubMeterial.KONWA + '/' + SubMeterial.KMEIN + case isNull(subMeterial.unit,'') when '' then '' else '/' + subMeterial.unit end  as Unit"
        sql &= " FROM Meterial,SubMeterial"
        sql &= " WHERE Meterial.Material = SubMeterial.Material"
        If (code_name <> "") Then
            sql &= " AND ((Meterial.Material Like '%' + @Material + '%') OR (Meterial.Text_Line Like '%' + @Material + '%') OR (Meterial.MATL_DESC Like '%' + @Material + '%'))"
        End If
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Material", SqlDbType.NVarChar, 40).Value = code_name
        adap.Fill(ds, "Material")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function Get_MeterailAllField(ByVal MATERIAL As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT * "
        sql &= " FROM Meterial"
        sql &= " WHERE Material = @MAT"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@MAT", SqlDbType.NVarChar, 18).Value = MATERIAL
        adap.Fill(ds, "Relation")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Owner(ByVal Booking_no As String, ByVal Owner As String, ByVal Material As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select * from BookingTariff"
        sql &= " where Booking_no = @Booking_no "
        sql &= " and Owner = @Owner "
        If (Material <> "") Then
            sql &= " and Material = @Material "
        End If
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.SelectCommand.Parameters.Add("@Owner", SqlDbType.NVarChar, 10).Value = Owner
        If (Material <> "") Then
            adap.SelectCommand.Parameters.Add("@Material", SqlDbType.NVarChar, 18).Value = Material
        End If

        adap.Fill(ds, "Owner")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_MeterailRelation(ByVal MATERIAL As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT * "
        sql &= " FROM RelationMeterial"
        sql &= " WHERE RelationMeterial.Meterial = @MAT"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@MAT", SqlDbType.NVarChar, 18).Value = MATERIAL
        adap.Fill(ds, "Relation")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_StardardTime(ByVal Product_Name As String, ByVal L_D As String, ByVal Group_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String = ""
        Dim ds As New DataSet
        'sql = "SELECT Product.* , Product_Group.* "
        'sql &= " FROM Product_Group,Product"
        'sql &= " WHERE Product.Product_name in (" & Product_Name & ")"
        'sql &= " AND Product.Group_no = Product_Group.Group_no"
        sql &= " SELECT Distinct Loading_Unloading.Group_Code,Loading_Facility.Rate,Loading_Facility.Before,Loading_Facility.After,Loading_Facility.Plus_Tank,Loading_Facility.Plus_Parcel_Before,Loading_Facility.Plus_Product_Before,Loading_Facility.Plus_Parcel_After,Loading_Facility.Plus_Product_After " & vbCrLf
        sql &= "  FROM Product,Loading_Unloading,Loading_Facility " & vbCrLf
        If (Group_no = "") Then
            sql &= " WHERE Product.Product_name in (" & Product_Name & ") " & vbCrLf
        Else
            sql &= " WHERE Product.Group_no = '" & Group_no & "' " & vbCrLf
        End If

        sql &= " AND Product.Group_no = Loading_Unloading.Group_Code " & vbCrLf
        sql &= " AND Loading_Unloading.Load_Code = Loading_Facility.Load_Code " & vbCrLf
        sql &= " AND Loading_Facility.L_D = '" & L_D & "' " & vbCrLf
        sql &= " AND Loading_Facility.Rate = ("
        sql &= " SELECT MAX(Rate) " & vbCrLf
        sql &= "  FROM Product,Loading_Unloading,Loading_Facility " & vbCrLf

        If (Group_no = "") Then
            sql &= " WHERE Product.Product_name in (" & Product_Name & ") " & vbCrLf
        Else
            sql &= " WHERE Product.Group_no = '" & Group_no & "' " & vbCrLf
        End If
        sql &= " AND Product.Group_no = Loading_Unloading.Group_Code " & vbCrLf
        sql &= " AND Loading_Unloading.Load_Code = Loading_Facility.Load_Code " & vbCrLf
        sql &= " AND Loading_Facility.L_D = '" & L_D & "') " & vbCrLf

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.Fill(ds, "StandardTime")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_StardardTimeBulk(ByVal Product_Name As String, ByVal TypePage As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT Product.* , Product_Group.* "
        sql &= " FROM Product_Group,Product"
        If (TypePage = "E") Then
            sql &= " WHERE Product.Product_name in (" & Product_Name & ")"
            sql &= " AND Product.Group_no = Product_Group.Group_no"
        Else
            sql &= " WHERE Product.Group_no in (" & Product_Name & ")"
            sql &= " AND Product.Group_no = Product_Group.Group_no"
        End If


        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.Fill(ds, "StandardTime")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_SubMeterail(ByVal MATERIAL As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT * "
        sql &= " FROM SubMeterial"
        sql &= " WHERE SubMeterial.Material = @MAT"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@MAT", SqlDbType.NVarChar, 18).Value = MATERIAL
        adap.Fill(ds, "Relation")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_VEHICLE_DETAIL(ByVal VEHICLE As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT * "
        sql &= " FROM Vessel"
        sql &= " WHERE Vessel.VEHICLE = @VEHICLE"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@VEHICLE", SqlDbType.NVarChar, 10).Value = VEHICLE
        adap.Fill(ds, "VEHICLE")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_MeterailMasterForEditPopup(ByVal code_name As String) As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT DISTINCT  Meterial.MATERIAL, Meterial.MAT_WHT, Meterial.MAT_Default, Meterial.TEXT_LINE, Meterial.MATL_DESC, Meterial.BASE_UOM, "
        sql &= "  Meterial.DELYG_PLNT, SubMeterial.UNIT, RelationMeterial.Mat_Name, RelationMeterial.Mat_Rate, "
        sql &= "  RelationMeterial.Mat_Code, RelationMeterial.Relation, RelationMeterial.Point_Start, RelationMeterial.Point_End,RelationMeterial.Point_Unit,RelationMeterial.Vessel_national,RelationMeterial.Vessel_Destination,RelationMeterial.Compute_Count,RelationMeterial.LowRate,RelationMeterial.LowRate_Money, "
        sql &= "  RelationMeterial.Money,RelationMeterial.Customer_no,RelationMeterial.Customer_name,RelationMeterial.Customer_Rate,RelationMeterial.Customer_Money,RelationMeterial.Compute_Between,RelationMeterial.Data3,Meterial.MAT_PORT,Meterial.MAT_FOR,Meterial.MAT_National, "
        sql &= " Meterial.MAT_BUY,Meterial.MAT_Product,Meterial.MAT_Load,Meterial.MAT_Disch,Meterial.MAT_ComputebuyerSeller,Meterial.MAT_ComputeNational,Meterial.MAT_Compute,Meterial.MAT_ComputeDestinaation,Meterial.MAT_ComputeLoad,Meterial.MAT_ComputeDisch,Meterial.MAT_VesselNation,Meterial.MAT_ComputeBuy"
        sql &= " ,Meterial.MAT_ComputeProduct,Meterial.MAT_ComputeProduct_Load,Meterial.MAT_ComputeProduct_Disch,RelationMeterial.Compute_C,Meterial.MAT_DWToperate,Meterial.MAT_Shifting,Meterial.MAT_Operate "
        sql &= " FROM         Meterial LEFT OUTER JOIN "
        sql &= "     SubMeterial ON Meterial.MATERIAL = SubMeterial.MATERIAL LEFT OUTER JOIN "
        sql &= "   RelationMeterial ON Meterial.MATERIAL = RelationMeterial.Meterial "
        sql &= " WHERE     (Meterial.MATERIAL = @Material) "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Material", SqlDbType.NVarChar, 18).Value = code_name
        adap.Fill(ds, "Materialpopup")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function Check_Stock_Empty() As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select * from stock "
        sql &= " where Active = 'Y'"
        sql &= " and (convert(Decimal(7,3),Amount)) < (convert(Decimal(7,3),1)) "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.Fill(ds, "Empty")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Mat_PRoduct(ByVal Mat As String, ByVal Type As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " Select * From Material_Product "
        sql &= " WHERE Material = @MAT"
        sql &= " AND Type = @Type "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@MAT", SqlDbType.NVarChar, 18).Value = Mat
        adap.SelectCommand.Parameters.Add("@Type", SqlDbType.NVarChar, 1).Value = Type
        adap.Fill(ds, "MAT_Product")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_IRPCBuyerSeller(ByVal BS_no As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " Select * From Buyer_Seller "
        sql &= " WHERE BS_no = @BS_no"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@BS_no", SqlDbType.NVarChar, 10).Value = BS_no
        adap.Fill(ds, "BS_no")
        adap = Nothing
        con.Close()
        If (ds.Tables(0).Rows.Count > 0) Then
            If (ds.Tables(0).Rows(0).Item("BS_Group") & "" = "I") Then
                Return "Y"
            Else
                Return "N"
            End If
        Else
            Return "N"
        End If
    End Function

    Public Function Get_Config() As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " Select * From Config "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        ' adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking
        adap.Fill(ds, "Config")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    'ใช้ทำการ Tariff ที่เป็นค่าตั้งต้นมาทำงาน
    Public Function Get_MeterailTariff() As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select M.MATERIAL,M.MAT_WHT,case M.Text_Line when '' Then M.MATL_DESC Else M.TEXT_LINE END as TEXT_LINE,S.KPEIN,S.KMEIN,S.POINT_UNIT,S.UNIT "
        sql &= " from Meterial M,subMeterial S"
        sql &= " WHERE M.MATERIAL = S.MATERIAL"
        sql &= " AND MAT_Default = 'Y'"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        ' adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking
        adap.Fill(ds, "Tariff")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function Get_MeterailWhithodingTax(ByVal MATERIAL As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select * "
        sql &= " from Meterial "
        sql &= " WHERE MATERIAL = @MATERIAL "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        Try
            MATERIAL = CInt(MATERIAL).ToString("000000000000000000")
        Catch ex As Exception

        End Try
        adap.SelectCommand.Parameters.Add("@MATERIAL", SqlDbType.NVarChar, 18).Value = MATERIAL
        adap.Fill(ds, "MATERIAL")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Sub byddlfromDataSet(ByRef ddl As DropDownList, ByVal ds As DataSet, ByVal Text As String, ByVal Value As String)
        If (ds.Tables(0).Rows.Count > 0) Then
            With ddl
                .DataTextField = Text
                .DataValueField = Value
                .DataSource = ds
                .DataBind()
                .Items.Insert(0, "<--กรุณาเลือก-->")
                .Items(0).Value = "0"
            End With
        Else
            With ddl
                .Items.Insert(0, "<--ไม่พบข้อมูล-->")
                .Items(0).Value = "0"
            End With
        End If

    End Sub

    Public Function LoadRole() As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select * "
        sql &= " from Role"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        ' adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking
        adap.Fill(ds, "Tariff")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function LoadMenu(ByVal Role As String, ByVal Mode As String) As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT     Menu.Menu_no, Menu.Menu_name,Menu.Mode,Menu.Path, Role_Menu.Show_Menu, Role_Menu.Edit_Menu, Role_Menu.Add_Menu, Role_Menu.Delete_Menu,Role_Menu.Approve_Menu "
        sql &= " FROM         Menu LEFT OUTER JOIN "
        sql &= "    Role_Menu ON Menu.Menu_no = Role_Menu.Menu_no AND Role_Menu.Role_no = @Role_no "
        sql &= " WHERE Mode in (" & Mode & ") "
        sql &= " ORDER by Menu.Mode,Menu.Menu_name "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Role_no", SqlDbType.NVarChar, 10).Value = Role
        adap.Fill(ds, "Tariff")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function GetUserIPORT(ByVal user_name As String, ByVal Eventcheckname As Boolean, ByVal Role As String) As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select distinct  Top 1000 UserIPORT.User_name,UserIPORT.Name,UserIPORT.Status,Role.Role_name,UserIPORT.Email,UserIPORT.Password,UserIPORT.Customer_no,UserIPORT.Customer_name "
        sql &= " from UserIPORT,User_Role,Role "
        sql &= " where UserIPORT.User_name = User_Role.User_name "
        If (Role <> "0") Then
            sql &= " and User_Role.Role_no = @Role "
        End If
        sql &= " and User_Role.Role_no = Role.Role_no "
        If Not (user_name = "") Then
            If (Eventcheckname) Then
                sql &= " and UserIPORT.User_name = @User_name "
            Else
                sql &= " and ((UserIPORT.User_name Like '%' + @User_name + '%') OR (UserIPORT.Name Like '%' + @User_name + '%')) "
            End If
        End If
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        If Not (user_name = "") Then
            adap.SelectCommand.Parameters.Add("@User_name", SqlDbType.NVarChar, 50).Value = user_name
        End If
        adap.SelectCommand.Parameters.Add("@Role", SqlDbType.NVarChar, 10).Value = Role
        adap.Fill(ds, "User")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function Get_PRODUCT(ByVal product As String, ByVal Group As String) As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select Product.*,Product_Group.Group_name "
        sql &= " from Product,Product_Group "
        sql &= " Where Product.Group_no = Product_Group.Group_no"
        If Not (product = "") Then
            sql &= " and (product.Product_name Like '%' + @User_name + '%') "
        End If
        If (Group <> "0") Then
            sql &= " and Product.Group_no = @Group_no "
        End If
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        If Not (product = "") Then
            adap.SelectCommand.Parameters.Add("@User_name", SqlDbType.NVarChar, 200).Value = product
        End If
        If (Group <> "0") Then
            adap.SelectCommand.Parameters.Add("@Group_no", SqlDbType.NVarChar, 10).Value = Group
        End If
        adap.Fill(ds, "Product")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function GetRoleUser(ByVal user_name As String) As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select User_Role.User_name,Role.Role_name,Role.Role_no from User_Role,Role "
        sql &= " where User_Role.Role_no = Role.ROle_no "
        sql &= " and User_Role.User_name = @User_name "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@User_name", SqlDbType.NVarChar, 50).Value = user_name
        adap.Fill(ds, "Menu")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function GetPaymentForCancel(ByVal Booking_no) As Boolean
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select * "
        sql &= " from Booking  WHERE Booking_no = @Booking_no"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "Menu")
        adap = Nothing
        con.Close()
        If (ds.Tables(0).Rows(0).Item("Status_Pay") & "" = "Y") Then
            If (ds.Tables(0).Rows(0).Item("Credit_Limit") & "" = "Y") Or (ds.Tables(0).Rows(0).Item("Credit_Limit") & "" = "N") Then
                Return True
            Else
                Return False
            End If
        Else
            Return False
        End If

    End Function

    Public Function Get_SaleORDER_IPort_Number(ByVal Booking_no As String, ByVal Customer As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        'sql = "select * "
        'sql &= " from SAP_Sale_Order  WHERE Booking_no = @Booking_no"
        'sql = " select Distinct S.*,M.TAXCLASS " & vbCrLf
        'sql &= " from SAP_Sale_Order S,Meterial M,SAP_Sale_Order_Material O " & vbCrLf
        'sql &= " where S.Booking_no = @Booking_no " & vbCrLf
        'If (Customer <> "") Then
        '    sql &= " and S.Sold_To = @Sold_To " & vbCrLf
        'End If

        'sql &= " and S.Iport_no = O.Iport_no " & vbCrLf
        'sql &= " and O.Material = M.Material " & vbCrLf

        sql = "SELECT DISTINCT S.*, M.TAXCLASS, ISNULL(C.BILL_NO, N'') AS BILL_NO "
        sql &= "FROM         dbo.SAP_Sale_Order AS S INNER JOIN "
        sql &= "                      dbo.SAP_Sale_Order_Material AS O ON S.Iport_no = O.Iport_no INNER JOIN "
        sql &= "                      dbo.Meterial AS M ON O.Material = M.MATERIAL LEFT OUTER JOIN "
        sql &= "                      dbo.SAP_IPORT_DOC AS C ON S.Iport_no = C.IPORTNO "
        sql &= "WHERE     (S.Booking_no = @Booking_no) "
        If (Customer <> "") Then sql &= " AND (S.Sold_To = @Sold_To) "
        sql &= "ORDER BY S.IPORT_NO ASC, S.EX_BILL_NO ASC"

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.SelectCommand.Parameters.Add("@Sold_To", SqlDbType.NVarChar, 10).Value = Customer & ""
        adap.Fill(ds, "Sale_ORDER_iport_no")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_SaleORDER_IPort_NumberFor_ReleseCashSale(ByVal Iport_no) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select * "
        sql &= " from SAP_Sale_Order  WHERE Iport_no = @Iport_no"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Iport_no", SqlDbType.NVarChar, 12).Value = Iport_no
        adap.Fill(ds, "Sale_ORDER_iport_no")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_SaleORDER_Material(ByVal Iport_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select SAP_Sale_Order_Material.*,SAP_Sale_Order.Sold_To "
        sql &= " from SAP_Sale_Order_Material,SAP_Sale_Order  WHERE SAP_Sale_Order_Material.Iport_no = @Iport_no AND SAP_Sale_Order_Material.Iport_no = SAP_Sale_Order.Iport_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Iport_no", SqlDbType.NVarChar, 12).Value = Iport_no
        adap.Fill(ds, "Sale_Order_Mat")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_SaleORDER_MaterialFor_SHowActual(ByVal Iport_no As String, ByVal Soldto As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select SAP_Sale_Order_Material.*,SAP_Sale_Order.Sold_To "
        sql &= " from SAP_Sale_Order_Material,SAP_Sale_Order  WHERE SAP_Sale_Order_Material.Iport_no = @Iport_no AND SAP_Sale_Order_Material.Iport_no = SAP_Sale_Order.Iport_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Iport_no", SqlDbType.NVarChar, 12).Value = Iport_no
        adap.Fill(ds, "Sale_Order_Mat")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function GetMENUiport() As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select * "
        sql &= " from Menu "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        ' adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking
        adap.Fill(ds, "Menu")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function GetBookingTariff_Actual(ByVal Booking As String, ByVal Material As String, ByVal Owner As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select * "
        sql &= " from BookingTariff_Actual "
        sql &= " WHERE Booking_no = @Booking_no "
        If (Material <> "") Then
            sql &= " AND MATERIAL Like '%' + @Material + '%' "
        End If
        If (Owner <> "") Then
            sql &= " AND Owner = @Owner "
        End If
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking
        If (Material <> "") Then
            adap.SelectCommand.Parameters.Add("@Material", SqlDbType.NVarChar, 18).Value = Material
        End If
        If (Owner <> "") Then
            adap.SelectCommand.Parameters.Add("@Owner", SqlDbType.NVarChar, 10).Value = Owner
        End If
        adap.Fill(ds, "Menu")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function GetActual_Data_Backup_For_Billing(ByVal Booking As String, ByVal Owner As String, ByVal chk As Boolean) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select * "
        sql &= " from Actual_Data_Backup_For_Billing "
        sql &= " WHERE Booking_no = @Booking_no "
        If (Owner <> "") Then
            sql &= " AND Owner = @Owner "

        End If
        If (chk) Then
            Dim B As String = ""
            Try
                B = Booking.Substring(0, 1)
            Catch ex As Exception

            End Try
            If (B = "B") Then
                sql &= " AND chk = 'Y' "
                sql &= " AND Owner <> '' "
            Else
                sql &= " AND chk <> 'N' "
            End If
        End If
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking
        If (Owner <> "") Then
            adap.SelectCommand.Parameters.Add("@Owner", SqlDbType.NVarChar, 10).Value = Owner
        End If
        adap.Fill(ds, "Actual_Data_Backup_For_Billing")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Load_Unload_Product_Bulk(ByVal Booking As String, ByVal Product_no As String, ByVal BP_Code As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select * "
        sql &= " from Load_Unload_Product_Bulk "
        sql &= " WHERE Booking_no = @Booking_no "
        If (Product_no <> "") Then
            sql &= " AND Product_no = @Product_no "
        End If
        If (BP_Code <> "") Then
            sql &= " AND BP_Code = @BP_Code "
        End If
        sql &= " ORDER By _Datesort "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking
        If (Product_no <> "") Then
            adap.SelectCommand.Parameters.Add("@Product_no", SqlDbType.NVarChar, 10).Value = Product_no
        End If
        If (BP_Code <> "") Then
            adap.SelectCommand.Parameters.Add("@BP_Code", SqlDbType.NVarChar, 25).Value = BP_Code
        End If
        adap.Fill(ds, "Load_Unload_Product_Bulk")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function GetTemp_Estimate(ByVal Booking As String, ByVal Owner As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select *,Material as id "
        sql &= " from Temp_Estimate "
        sql &= " WHERE Booking_no = @Booking_no "
        If (Owner <> "") Then
            sql &= " AND Owner_Product = @Owner_Product "
        End If
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking
        If (Owner <> "") Then
            adap.SelectCommand.Parameters.Add("@Owner_Product", SqlDbType.NVarChar, 10).Value = Owner
        End If
        adap.Fill(ds, "Temp_Estimate")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function


    Public Function GetTemp_EstimateOwner(ByVal Booking As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select Distinct Owner_Product "
        sql &= " from Temp_Estimate "
        sql &= " WHERE Booking_no = @Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking
        adap.Fill(ds, "Temp_Estimate")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function GetTemp_EstimateOwnerForOldEstimate(ByVal Booking As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select Distinct C.Customer_No as Owner_Product_no ,C.Name1 + '' + C.Name2 + '' + C.Name3 as Owner_Product_name "
        sql &= "  from Temp_Estimate T,Customer C "
        sql &= " WHERE T.Booking_no = @Booking_no "
        sql &= " and T.Owner_Product = C.Customer_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking
        adap.Fill(ds, "Temp_Estimate")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function GetActualTIme(ByVal Booking As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select I.Berthing_Date,I.Berthing_TimeFrom,I.Berthing_TimeTo,L.Depart_Actual_Date,L.Depart_Actual_Time from Booking_LogSheet_In I,Load_Unload_Vessel L "
        sql &= " WHERE I.Booking_no = @Booking_no and I.Booking_no = L.Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking
        adap.Fill(ds, "ActualTIme")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function GetBlock_CreditLimit() As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select Distinct S.*,M.MESSAGE  "
        sql &= "  from SAP_Sale_Order S,SAP_Table_MESSAGE M "
        sql &= " WHERE S.Credit_Limit = 'N' "
        sql &= " AND S.Iport_no = M.Iport_no "
        sql &= " And M.Type = 'E' "
        sql &= " ANd M.ID = 'VF' "
        sql &= " AND M.Number = '140' "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.Fill(ds, "Credit_Limit")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function GetName_Customer(ByVal customer As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select * "
        sql &= " from Customer "
        sql &= " WHERE Customer_no = @Customer_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Customer_no", SqlDbType.NVarChar, 10).Value = CInt(customer).ToString("0000000000")
        adap.Fill(ds, "Customer_Name")
        adap = Nothing
        con.Close()
        If (ds.Tables(0).Rows.Count > 0) Then
            Return ds.Tables(0).Rows(0).Item("Customer_no") & " - " & ds.Tables(0).Rows(0).Item("NAME1") & " " & ds.Tables(0).Rows(0).Item("NAME2") & " " & ds.Tables(0).Rows(0).Item("NAME3")
        Else
            Return ""
        End If

    End Function

    Public Function Get_Basno(ByVal Bas_name As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select * "
        sql &= " from Bas_Master "
        sql &= " WHERE Bas_Name = @Bas_Name "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Bas_Name", SqlDbType.NVarChar, 500).Value = Bas_name
        adap.Fill(ds, "Bas_Name")
        adap = Nothing
        con.Close()
        If (ds.Tables(0).Rows.Count > 0) Then
            Return ds.Tables(0).Rows(0).Item("Bas_no")
        Else
            Return ""
        End If

    End Function

    Public Function Get_Bas_Vessel_In(ByVal BV_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select Distinct B.*,M.Bas_name,B.StartDate + ' - ' + B.StartTime as StartShow , B.EndDate + ' - ' + B.EndTime as EndShow from Bas_Vessel_In B,Bas_Master M "
        sql &= " where B.BV_no = @BV_no "
        sql &= " AND B.Bas_no = M.Bas_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@BV_no", SqlDbType.NVarChar, 10).Value = BV_no
        adap.Fill(ds, "Bas_Name")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)

    End Function

    Public Function GetMoneySumNet(ByVal Iport_no As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select * "
        sql &= " from SAP_Sale_Order_Material "
        sql &= " WHERE Iport_no = @Iport_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Iport_no", SqlDbType.NVarChar, 12).Value = Iport_no
        adap.Fill(ds, "MatNet")
        adap = Nothing
        con.Close()
        If (ds.Tables(0).Rows.Count > 0) Then
            Dim Net As Double = 0.0
            For Each dr As DataRow In ds.Tables(0).Rows
                Net = Net + (CDbl((dr.Item("Amount")) - CDbl(dr.Item("Discount"))) * CDbl(dr.Item("Target_Qty")))
            Next
            Return Net.ToString()
        Else
            Return "0.0"
        End If

    End Function

    Public Function GetOrderFor_CreateCNDN(ByVal Booking_no As String, ByVal Material As String, ByVal Sold_To As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select * "
        sql &= " from SAP_Sale_Order S,SAP_Sale_Order_Material M "
        sql &= " where S.Booking_no = @Booking_no "
        If (Sold_To <> "") Then
            sql &= " AND S.Sold_To = @Sold_To "
        End If
        'sql &= " And M.Iport_no Not in (select Old_Iport_no from SAP_CNDN_REQUEST where Booking_no = @Booking_no) "
        sql &= " AND M.Iport_no = S.Iport_no "
        sql &= " ANd M.Material in (" & Material & ") "
        sql &= " ORDER By Sold_To "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.SelectCommand.Parameters.Add("@Sold_To", SqlDbType.NVarChar, 10).Value = Sold_To
        adap.Fill(ds, "Create_CNDN")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function GetOrderFor_CreateNewInvoice(ByVal Booking_no As String, ByVal Sold_To As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select Distinct S.*,B.Vehicle_Text "
        sql &= " from SAP_Sale_Order S,Booking B "
        sql &= " where  S.Booking_no = @Booking_no "
        sql &= " AND S.Sold_To = @Sold_To "
        sql &= " AND S.Booking_no = B.Booking_no "
        sql &= " Order by S.Iport_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.SelectCommand.Parameters.Add("@Sold_To", SqlDbType.NVarChar, 10).Value = Sold_To
        adap.Fill(ds, "Create_CNDN")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Bill_TO_Bulk(ByVal Booking_no As String, ByVal Owner As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select * "
        sql &= " from Booking_Product "
        sql &= " where  Booking_no = @Booking_no "
        sql &= " AND Owner_Product_no = @Owner "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.SelectCommand.Parameters.Add("@Owner", SqlDbType.NVarChar, 10).Value = Owner
        adap.Fill(ds, "Owner_Proudct")
        adap = Nothing
        con.Close()
        If (ds.Tables(0).Rows.Count > 0) Then
            Return ds.Tables(0).Rows(0).Item("Billing_No") & ""
        Else
            Return "0"
        End If
    End Function

    Public Function GetBlock_CNDNByOne(ByVal Booking_no As String, ByVal Owner As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select B.Booking_no,B.MAgent_no,B.MAgent_name,S.IM_IPORTNUM,S.IM_REASON,S.IM_REF_DOC,S.EX_SALES_NO,S.IM_DOCTYPE,I.MATERIAL,Case M.Text_LINE when '' then MATL_DESC Else Text_LINE END as Text_Line,I.Target_Qty,I.Target_QU,I.Amount,S.CNDN_Bill " & vbCrLf
        sql &= " from Booking B,SAP_CNDN_REQUEST S,SAP_CNDN_ITEM_REQUEST I,Meterial M " & vbCrLf
        sql &= " where B.Booking_no = @Booking_no " & vbCrLf
        sql &= " and B.Booking_no = S.Booking_no " & vbCrLf
        sql &= " and S.Sold_to = @Owner " & vbCrLf
        sql &= " and S.IM_IPORTNUM = I.IM_IPORTNUM " & vbCrLf
        sql &= " and I.MATERIAL = M.MATERIAL " & vbCrLf
        sql &= " Order by M.Material " & vbCrLf
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.SelectCommand.Parameters.Add("@Owner", SqlDbType.NVarChar, 10).Value = Owner
        adap.Fill(ds, "CNDN")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function GetBlock_CNDN_Day(ByVal DateFrom As String, ByVal DateTo As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select B.Booking_no,B.MAgent_no,B.MAgent_name,S.Sold_to,S.IM_IPORTNUM,S.IM_REASON,S.IM_REF_DOC,S.EX_SALES_NO,S.IM_DOCTYPE,I.MATERIAL,Case M.Text_LINE when '' then MATL_DESC Else Text_LINE END as Text_Line,I.Target_Qty,I.Target_QU,I.Amount, S.CNDN_Bill " & vbCrLf
        sql &= " from Booking B,SAP_CNDN_REQUEST S,SAP_CNDN_ITEM_REQUEST I,Meterial M " & vbCrLf
        sql &= " where  B.Booking_no = S.Booking_no " & vbCrLf
        sql &= " and S.IM_IPORTNUM = I.IM_IPORTNUM " & vbCrLf
        sql &= " and I.MATERIAL = M.MATERIAL " & vbCrLf
        sql &= " and SubString(S.IM_IPORTNUM,1,8) Between " & DateFrom & " and " & DateTo & " " & vbCrLf
        sql &= " Order by B.Booking_no,S.Sold_to,M.Material " & vbCrLf
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.Fill(ds, "CNDN")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function GetBlock_CNDNAllRecord() As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select Distinct Booking_no,IM_REF_DOC,sold_To " & vbCrLf
        sql &= " from SAP_CNDN_REQUEST " & vbCrLf
        sql &= " WHERE RELEASE = 'N' " & vbCrLf
        sql &= " Order by Booking_no " & vbCrLf
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.Fill(ds, "CNDN")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function GetSaleOrder_ForRelease(ByVal Booking_no As String, ByVal Owner As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select Distinct EX_SALES_NO,IM_IPORTNUM " & vbCrLf
        sql &= " from SAP_CNDN_REQUEST " & vbCrLf
        sql &= " where Booking_no = @Booking_no " & vbCrLf
        sql &= " And sold_to = @Owner "
        sql &= " And RELEASE <> 'Y' "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.SelectCommand.Parameters.Add("@Owner", SqlDbType.NVarChar, 10).Value = Owner
        adap.Fill(ds, "CNDN")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function GetSaleOrder_Billing_Today(ByVal Billing_Date As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select S.EX_BILL_NO as Billing_no,S.Doc_Type,S.Booking_no,S.Iport_no,S.Ship_Name as Vessel_Name,C.Name1 + C.Name2 + C.Name3 as Agent,'' as Tax from SAP_Sale_Order S,Customer C " & vbCrLf
        sql &= " where S.Bill_To = C.Customer_no " & vbCrLf
        sql &= " and S.EX_Bill_NO <> '' " & vbCrLf
        sql &= " and S.Billing_Date = @Billing_Date"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Billing_Date", SqlDbType.NVarChar, 8).Value = Billing_Date
        adap.Fill(ds, "CNDN")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function


    Public Function GetVehicle_TextByBilling(ByVal Billing As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select Distinct B.Booking_no,B.Vehicle_Text " & vbCrLf
        sql &= " from SAP_Sale_Order S,Booking B " & vbCrLf
        sql &= " where EX_Bill_no = @Billing " & vbCrLf
        sql &= " and s.Booking_no = B.Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Billing", SqlDbType.NVarChar, 10).Value = Billing
        adap.Fill(ds, "Billing")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function GetBASE_UOM_FromMaterial(ByVal Material As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select Base_UOM " & vbCrLf
        sql &= " from Meterial " & vbCrLf
        sql &= " where Material = @Material " & vbCrLf
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Material", SqlDbType.NVarChar, 18).Value = Material
        adap.Fill(ds, "Billing")
        adap = Nothing
        con.Close()
        If ds.Tables(0).Rows.Count > 0 Then
            Return ds.Tables(0).Rows(0).Item("Base_UOM")
        Else
            Return ""
        End If

    End Function

    Public Function GetMaterialAllByOne(ByVal Material As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select * " & vbCrLf
        sql &= " from Meterial " & vbCrLf
        sql &= " where Material = @Material " & vbCrLf
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Material", SqlDbType.NVarChar, 18).Value = Material
        adap.Fill(ds, "Billing")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function GetCustomerPayinManual(ByVal Customer As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " Select Distinct T.Owner_Product,C.Name1 + '' + C.Name2 + '' + C.Name3 as Customer_Name,B.Booking_no,B.Vehicle_Text,B.Booking_Date,B.Booking_Time " & vbCrLf
        sql &= " From Temp_Estimate T,Booking B,Customer C " & vbCrLf
        sql &= " where T.Booking_no = B.Booking_no " & vbCrLf
        sql &= " and B.Status_Pay <> 'Y' " & vbCrLf
        'sql &= " and B.Payin = '0' " & vbCrLf
        '********** เช็คว่า MAgent_no กับ Ower_Product_No เหมือนกันหรือไม่ ถ้าไม่เหมือกันให้เช็ค Payin ทั้งสองตาราง
        sql &= "AND " & vbCrLf
        sql &= "(CASE WHEN Owner_Product = (SELECT MAgent_no FROM Booking WHERE MAgent_no = T.Owner_Product AND Booking_no = T.Booking_no) THEN (SELECT Payin FROM Booking WHERE MAgent_no = T.Owner_Product AND Booking_no = T.Booking_no) " & vbCrLf
        sql &= "	  WHEN Owner_Product = (SELECT Owner_Product_no FROM Booking_Product WHERE Owner_Product_no = T.Owner_Product AND Booking_no = T.Booking_no) THEN (SELECT Type_Pay FROM Booking_Product WHERE Owner_Product_no = T.Owner_Product AND Booking_no = T.Booking_no) " & vbCrLf
        sql &= " END) = '0' " & vbCrLf
        ''***********************************************************
        sql &= " and T.Owner_Product = C.Customer_no  " & vbCrLf
        sql &= " and T.Owner_Product Not in ('9999999999') " & vbCrLf
        If (Customer <> "") Then
            sql &= " and ((C.Customer_no Like '%' + @Customer + '%') OR ((C.Name1 + '' + C.Name2 + '' + C.Name3) Like '%' + @Customer + '%'))"
        End If
        sql &= " and (Owner_Product + B.Booking_no) Not in ( Select M.Customer + S.Booking_no from SAP_Advanced_Payment S,SAP_Advanced_Payment_Material M    " & vbCrLf
        sql &= " Where S.Iport_no = M.Iport_no ) " & vbCrLf

        'sql = " Select Distinct T.Owner_Product,C.Name1 + '' + C.Name2 + '' + C.Name3 as Customer_Name,B.Booking_no,B.Vehicle_Text,B.Booking_Date,B.Booking_Time " & vbCrLf
        'sql &= "  From Temp_Estimate T,Booking B,Customer C,SAP_Advanced_Payment S,SAP_Advanced_Payment_Material M " & vbCrLf
        'sql &= " where T.Booking_no = B.Booking_no " & vbCrLf
        'sql &= " and B.Booking_no = S.Booking_no " & vbCrLf
        'sql &= " and S.Iport_no = M.Iport_no " & vbCrLf
        'sql &= " and Owner_Product Not in (M.Customer) " & vbCrLf
        'sql &= " and B.Status_Pay <> 'Y' " & vbCrLf
        'sql &= " and B.Payin = '0' " & vbCrLf
        'sql &= " and T.Owner_Product = C.Customer_no  " & vbCrLf
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        If (Customer <> "") Then
            adap.SelectCommand.Parameters.Add("@Customer", SqlDbType.NVarChar, 200).Value = Customer
        End If
        adap.Fill(ds, "PayinManual")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function GetPayinAdvance(ByVal Booking_no As String, ByVal Sold_To As String) As Boolean
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select * from SAP_Advanced_Payment A,SAP_Advanced_Payment_Material M " & vbCrLf
        sql &= " where A.Booking_no = @Booking_no " & vbCrLf
        sql &= " And A.Iport_no = M.Iport_no " & vbCrLf
        sql &= " and M.Customer = @Sold_To " & vbCrLf
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        Try
            Sold_To = CInt(Sold_To)
        Catch ex As Exception

        End Try
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.SelectCommand.Parameters.Add("@Sold_To", SqlDbType.NVarChar, 10).Value = Sold_To

        adap.Fill(ds, "PayinManual")
        adap = Nothing
        con.Close()
        If (ds.Tables(0).Rows.Count > 0) Then
            'เจอ
            Return False
        Else
            Return True
        End If
    End Function

    Public Function GetMAT_FOR_FromMaterial(ByVal Material As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select MAT_FOR " & vbCrLf
        sql &= " from Meterial " & vbCrLf
        sql &= " where Material = @Material " & vbCrLf
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Material", SqlDbType.NVarChar, 18).Value = Material
        adap.Fill(ds, "Billing")
        adap = Nothing
        con.Close()
        If ds.Tables(0).Rows.Count > 0 Then
            Return ds.Tables(0).Rows(0).Item("MAT_FOR")
        Else
            Return ""
        End If

    End Function

    Public Function Get_TugBoat(ByVal Tug_Name As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select * " & vbCrLf
        sql &= " from Tug " & vbCrLf
        sql &= " where Tug_Name = @Tug_Name " & vbCrLf
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Tug_Name", SqlDbType.NVarChar, 200).Value = Tug_Name
        adap.Fill(ds, "Tug_Name")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)

    End Function

    Public Function Get_OtherService(ByVal Ots_ID As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select * " & vbCrLf
        sql &= " from OtherService_Head " & vbCrLf
        sql &= " where Ots_ID = @Ots_ID " & vbCrLf
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Ots_ID", SqlDbType.NVarChar, 10).Value = Ots_ID
        adap.Fill(ds, "OtherService")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_OtherService_Item(ByVal Ots_ID As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select * " & vbCrLf
        sql &= " from OtherService " & vbCrLf
        sql &= " where Ots_ID = @Ots_ID " & vbCrLf
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Ots_ID", SqlDbType.NVarChar, 200).Value = Ots_ID
        adap.Fill(ds, "OtherService")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_OtherService_ItemBooking(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select OtherService.* " & vbCrLf
        sql &= " from OtherService,OtherService_Head " & vbCrLf
        sql &= " where OtherService_Head.Booking_no = @Booking_no " & vbCrLf
        sql &= " and OtherService_Head.Ots_ID = OtherService.Ots_ID"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "OtherService")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Booking_LogSheet_Tug(ByVal Booking_no As String, ByVal T As String, ByVal EV As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select *,Date_Time + '/' + Start_Time + ' - ' + End_Time as Tug_Time from Booking_LogSheet_Tug "
        sql &= " where Booking_no = @Booking_no "
        If (T <> "") Then
            sql &= " AND Type = @Type  "
        End If
        If (EV <> "") Then
            sql &= " AND Tug_Event = @Tug_Event "
        End If
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        If (T <> "") Then
            adap.SelectCommand.Parameters.Add("@Type", SqlDbType.NVarChar, 1).Value = T
        End If
        If (EV <> "") Then
            adap.SelectCommand.Parameters.Add("@Tug_Event", SqlDbType.NVarChar, 1).Value = EV
        End If
        adap.Fill(ds, "Booking_LogSheet_Tug")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_For_DailyReport(ByVal _Date As String, ByVal _Date2 As String, ByVal _Time As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  SELECT      Booking.Booking_no, Booking.Vehicle_no, Booking.Vehicle_Text, Wharf.Wharf,  "
        sql &= "  Booking_LogSheet_In.Berthing_Date, Booking_LogSheet_In.Berthing_TimeFrom, Booking_LogSheet_In.Berthing_TimeTo,  "
        sql &= "  Load_Unload_Vessel.Depart_Actual_Date, Load_Unload_Vessel.Depart_Actual_Time "
        sql &= " FROM         Wharf INNER JOIN "
        sql &= "   BookingWharf ON Wharf.BookingWharf = BookingWharf.BookingWharf RIGHT OUTER JOIN "
        sql &= "   Booking LEFT OUTER JOIN "
        sql &= "   Load_Unload_Vessel ON Booking.Booking_no = Load_Unload_Vessel.Booking_no LEFT OUTER JOIN "
        sql &= "  Booking_LogSheet_In ON Booking.Booking_no = Booking_LogSheet_In.Booking_no ON BookingWharf.Booking_no = Booking.Booking_no "
        sql &= " where Booking.Status in ('MO','OA','FN') "
        sql &= " and Booking.PORT = 'L' "
        sql &= " and Booking_LogSheet_In.Berthing_Date <> '' "
        If (_Date <> "") And (_Time <> "") Then
            ' sql &= " and Booking_LogSheet_In.Berthing_Date = '" & _Date & "' "
            sql &= "  and  CONVERT(VARCHAR(10), Convert(DateTime,Booking_LogSheet_In.Berthing_Date) , 112) between " & _Date2 & " and " & _Date
            sql &= "  and (CONVERT(VARCHAR(10), Convert(DateTime,Booking_LogSheet_In.Berthing_Date) , 112) = '" & _Date2 & "' and Substring(Booking_LogSheet_In.Berthing_TimeTo,1,2) + Substring(Booking_LogSheet_In.Berthing_TimeTo,4,2) > " & _Time & ") "
            sql &= " Or  (CONVERT(VARCHAR(10), Convert(DateTime,Booking_LogSheet_In.Berthing_Date) , 112) = '" & _Date & "' and Substring(Booking_LogSheet_In.Berthing_TimeTo,1,2) + Substring(Booking_LogSheet_In.Berthing_TimeTo,4,2) < " & _Time & ") "
        End If
        sql &= " Order by Wharf "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        ' adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "Daily")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function


    Public Function Get_Booking_LogSheet_In(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Booking_LogSheet_In "
        sql &= " where Booking_no = @Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "Booking_LogSheet_Tug")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_CauseOfDelay() As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from CauseOfDelay ORDER BY Cause_Delay "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.Fill(ds, "CauseOfDelay")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Booking_LogSheet_Out(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Booking_LogSheet_Out "
        sql &= " where Booking_no = @Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "Booking_LogSheet_Tug")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_BookingWharf(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select B.*,W.Wharf,W.PORT from BookingWharf B,Wharf W "
        sql &= " where B.Booking_no = @Booking_no "
        sql &= " and B.BookingWharf = W.BookingWharf "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "BookingWharf")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Wharf_Shifting(ByVal Booking_no) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select Shift_Booking from BookingWharf "
        sql &= " where Booking_no = @Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "BookingWharf")
        con.Close()
        Dim Text As String = ""
        If (ds.Tables(0).Rows.Count > 0) Then
            If (ds.Tables(0).Rows(0).Item("Shift_Booking") & "" <> "") Then
                Dim dt As DataTable = Get_BookingWharf(ds.Tables(0).Rows(0).Item("Shift_Booking") & "")
                If (dt.Rows.Count > 0) Then
                    Text = "Shift To-" & dt.Rows(0).Item("Wharf") & ""
                End If
            Else
                ds = New DataSet
                con = New SqlConnection(connString)
                sql = "  select Booking_no from BookingWharf "
                sql &= " where Shift_Booking = @Booking_no "
                adap.SelectCommand = New SqlCommand(sql, con)
                adap.SelectCommand.Parameters.Clear()
                adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
                adap.Fill(ds, "BookingWharf")
                adap = Nothing
                con.Close()
                If (ds.Tables(0).Rows.Count > 0) Then
                    Dim dt As DataTable = Get_BookingWharf(ds.Tables(0).Rows(0).Item("Booking_no") & "")
                    If (dt.Rows.Count > 0) Then
                        Text = "Shift From-" & dt.Rows(0).Item("Wharf") & ""
                    End If
                End If
            End If
        End If
        Return Text
    End Function

    Public Function Get_Booking_LogSheet(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * from Booking_LogSheet "
        sql &= " where Booking_no = @Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "Booking_LogSheet")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_StandardTimeLiquid(ByVal Shore_Code As String, ByVal Group_Code As String, ByVal Line_Code As String, ByVal L_D As String, ByVal BookingWharf As String, ByVal Facility_Code As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "  select * "
        sql &= " from Loading_Unloading L,Loading_Facility F "
        sql &= " where Shore_Code = @Shore_Code "
        sql &= " and Group_Code = @Group_Code "
        sql &= " and Line_Code = @Line_Code "
        sql &= " and L.Load_Code = F.Load_Code "
        sql &= " and F.L_D = @L_D "
        sql &= " and F.BookingWharf = @BookingWharf "
        sql &= " and F.Facility_Code = @Facility_Code "

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Shore_Code", SqlDbType.NVarChar, 10).Value = Shore_Code
        adap.SelectCommand.Parameters.Add("@Group_Code", SqlDbType.NVarChar, 10).Value = Group_Code
        adap.SelectCommand.Parameters.Add("@Line_Code", SqlDbType.NVarChar, 10).Value = Line_Code
        adap.SelectCommand.Parameters.Add("@L_D", SqlDbType.NVarChar, 1).Value = L_D
        adap.SelectCommand.Parameters.Add("@BookingWharf", SqlDbType.NVarChar, 5).Value = BookingWharf
        adap.SelectCommand.Parameters.Add("@Facility_Code", SqlDbType.NVarChar, 10).Value = Facility_Code
        adap.Fill(ds, "Get_StandardTimeLiquid")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function ConvertDataViewToDataSet(ByVal dv As DataView) As DataTable
        Dim dtNew As DataTable = dv.Table.Clone()
        For Each drv As DataRowView In dv
            dtNew.ImportRow(drv.Row)
        Next
        dv.Dispose()
        Return dtNew
    End Function

    Public Sub GetStatus_Working(ByVal Booking_no As String, ByVal PORT As String, ByRef Status As String, ByRef Tooltip As String)
        If Not (Booking_no & "" = "") Then
            If (PORT = "L") Then
                Dim dt As DataTable = Get_Booking_LogSheet_In(Booking_no)
                If (dt.Rows.Count > 0) Then
                    Status = ""
                    If (dt.Rows(0).Item("NOR_Date") & "" <> "") Then
                        Status = "1 NOR TENDERED"
                    End If
                    If (dt.Rows(0).Item("Berthing_Date") & "" <> "") Then
                        Status = "2 BERTHED"
                    End If
                    dt = Get_Load_Unload_Vessel_Product_Detail(Booking_no)
                    If (dt.Rows.Count > 0) Then
                        If (dt.Rows(0).Item("LU_Connect_Date") & "" <> "") Then
                            Status = "3 CONNECT MLA/HOSE"
                        End If
                        If (dt.Rows(0).Item("LU_StartActual_Date") & "" <> "") Then
                            Status = "4 STARTED S/L ,S/U"
                        End If
                        If (dt.Rows(0).Item("LU_StopActual_Date") & "" <> "") Then
                            Status = "5 STOPPED S/L ,S/U"
                        End If
                        If (dt.Rows(0).Item("LU_Disconnect_Date") & "" <> "") Then
                            Status = "6 DISCONNECT MLA/HOSE"
                        End If
                    End If
                    dt = Get_Load_Unload_Vessel(Booking_no)
                    If (dt.Rows.Count > 0) Then
                        If (dt.Rows(0).Item("Depart_Actual_Date") & "" <> "") Then
                            Status = "7 DEPARTED (LCT)"
                        End If
                    End If

                    dt = Get_Booking_LogSheet_Out(Booking_no)
                    If (dt.Rows.Count > 0) Then
                        If (dt.Rows(0).Item("Last_Date") & "" <> "") Then
                            dt = Get_BookingOneRecord(Booking_no).Tables(0)
                            If (dt.Rows(0).Item("Status") & "" = "OA") Or (dt.Rows(0).Item("Status") & "" = "FN") Then
                                Status = "8 DEPARTED (PTO) CLOSE JOB"
                            End If
                        End If
                    End If

                End If

                Tooltip = "1 NOR TENDERED " & Environment.NewLine & "2 BERTHED" & Environment.NewLine & "3 CONNECT MLA/HOSE" & Environment.NewLine & "4 STARTED S/L ,S/U" & Environment.NewLine & "5 STOPPED S/L ,S/U" & Environment.NewLine & "6 DISCONNECT MLA/HOSE" & Environment.NewLine & "7 DEPARTED (LCT)" & Environment.NewLine & "8 DEPARTED (PTO) CLOSE JOB"
            Else 'Bulk
                Status = ""
                Dim dt As DataTable = Get_Booking_LogSheet_In(Booking_no)
                If (dt.Rows.Count > 0) Then
                    If (dt.Rows(0).Item("NOR_Date") & "" <> "") Then
                        'พบ NOR แสดงว่าเรือมาถึงท่าแล้ว
                        Status = "1 NOR TENDERED"
                    End If
                    If (dt.Rows(0).Item("Berthing_Date") & "" <> "") Then
                        'เรือเข้าเทียบท่า
                        Status = "2 BERTHED"
                    End If
                    dt = Get_Load_Unload_Vessel_Product_Detail_FOr_Status(Booking_no)
                    If (dt.Rows.Count > 0) Then
                        If (dt.Rows(0).Item("LU_StartActual_Date") & "" <> "") Then
                            Status = "3 COMMEND LOAD/DISCHARGE"
                            Dim dtNew As DataTable = Get_Load_Unload_Vessel(Booking_no)
                            If (dtNew.Rows.Count > 0) Then
                                If (dtNew.Rows(0).Item("Status") & "" = "S") Or (dtNew.Rows(0).Item("Status") & "" = "C") Or (dtNew.Rows(0).Item("Status") & "" = "A") Then
                                    If (dt.Rows(0).Item("LU_StopActual_Date") & "" <> "") Then
                                        Status = "4 COMPLETED LOAD/DISCHARGE"
                                    End If
                                End If
                            End If
                            dt = Get_Booking_LogSheet_Out(Booking_no)
                            If (dt.Rows.Count > 0) Then
                                If (dt.Rows(0).Item("Last_Date") & "" <> "") Then
                                    dt = Get_BookingOneRecord(Booking_no).Tables(0)
                                    If (dt.Rows(0).Item("Status") & "" = "OA") Or (dt.Rows(0).Item("Status") & "" = "FN") Then
                                        Status = "5 DEPARTED (PTO) CLOSE JOB"
                                    End If
                                End If
                            End If
                        End If
                    Else
                        dt = Get_Load_Unload_Vessel_Product_Detail_Bunker(Booking_no, "")
                        If (dt.Rows.Count > 0) Then
                            If (dt.Rows(0).Item("LU_StartActual_Date") & "" <> "") Then
                                Status = "3 COMMEND LOAD/DISCHARGE"
                                If (dt.Rows(0).Item("LU_StopActual_Date") & "" <> "") Then
                                    Status = "4 COMPLETED LOAD/DISCHARGE"

                                End If
                            End If
                        End If
                        dt = Get_Booking_LogSheet_Out(Booking_no)
                        If (dt.Rows.Count > 0) Then
                            If (dt.Rows(0).Item("Last_Date") & "" <> "") Then
                                dt = Get_BookingOneRecord(Booking_no).Tables(0)
                                If (dt.Rows(0).Item("Status") & "" = "OA") Or (dt.Rows(0).Item("Status") & "" = "FN") Then
                                    Status = "5 DEPARTED (PTO) CLOSE JOB"
                                End If
                            End If
                        End If
                    End If
                End If
                Tooltip = "1 NOR TENDERED " & Environment.NewLine & "2 BERTHED" & Environment.NewLine & "3 COMMEND LOAD/DISCHARGE" & Environment.NewLine & "4 COMPLETED LOAD/DISCHARGE" & Environment.NewLine & "5 DEPARTED(PTO)CLOSE JOB"
            End If
        End If
    End Sub

    Public Function ComputeRemainLaytimeBulk(ByVal Laytime As String, ByVal Booking_no As String) As String
        Try
            Dim LY As Integer = CInt(Laytime)
            Dim dtLoad As DataTable = Get_Load_Unload_Vessel(Booking_no)
            Dim dtLogIn As DataTable = Get_Booking_LogSheet_In(Booking_no)
            Dim NOR As String = ""
            Dim NOR_Time As String = ""
            If (dtLoad.Rows.Count > 0) Then
                If (dtLoad.Rows(0).Item("NOR_Date") & "" <> "") Then
                    NOR = dtLoad.Rows(0).Item("NOR_Date").ToString.Substring(0, 2) & " " & GetNameMonth(dtLoad.Rows(0).Item("NOR_Date").ToString.Substring(3, 2)) & " 20" & dtLoad.Rows(0).Item("NOR_Date").ToString.Substring(6, 2)
                    NOR_Time = dtLoad.Rows(0).Item("NOR_Time") & ""
                Else
                    NOR = dtLogIn.Rows(0).Item("NOR_Date") & ""
                    NOR_Time = dtLogIn.Rows(0).Item("NOR_Time") & ""
                End If
            Else
                NOR = dtLogIn.Rows(0).Item("NOR_Date") & ""
                NOR_Time = dtLogIn.Rows(0).Item("NOR_Time") & ""
            End If

            Dim New_AllFast As String = (CInt(NOR_Time.Split(":")(0)) + 12) & ":" & NOR_Time.Split(":")(1)
            Dim New_AllFast_Date As String
            If (CInt(New_AllFast.Split(":")(0)) > 23) Then
                New_AllFast_Date = CDate(NOR.Split(" ")(0) & " " & NOR.Split(" ")(1) & " " & CInt(NOR.Split(" ")(2))).AddDays(1).ToString("dd MMM yyyy", New CultureInfo("en-US"))
                New_AllFast = (CInt(NOR_Time.Split(":")(0)) + 12) Mod 24 & ":" & NOR_Time.Split(":")(1)
            Else
                New_AllFast_Date = NOR
            End If


            Dim dtProductBulk As DataTable = Get_Load_Unload_Vessel_Product_Detail(Booking_no)
            If (dtProductBulk.Rows.Count > 0) Then
                For Each dr As DataRow In dtProductBulk.Rows
                    Dim Commend_Date As String = dr.Item("LU_StartActual_Date").ToString.Substring(0, 2) & " " & GetNameMonth(dr.Item("LU_StartActual_Date").ToString.Substring(3, 2)) & " 20" & dr.Item("LU_StartActual_Date").ToString.Substring(6, 2)
                    Dim Commend_time As String = dr.Item("LU_StartActual_Time")
                    If (CDate(Commend_Date) > CDate(New_AllFast_Date)) Then
                        Commend_Date = New_AllFast_Date
                        Commend_time = New_AllFast
                    ElseIf (CDate(Commend_Date) = CDate(New_AllFast_Date)) Then
                        If (CInt(Commend_time.Split(":")(0)) > CInt(New_AllFast.Split(":")(0))) Then
                            Commend_Date = New_AllFast_Date
                            Commend_time = New_AllFast
                        ElseIf (CInt(Commend_time.Split(":")(0)) = CInt(New_AllFast.Split(":")(0))) Then
                            If (CInt(Commend_time.Split(":")(1)) > CInt(New_AllFast.Split(":")(1))) Then
                                Commend_Date = New_AllFast_Date
                                Commend_time = New_AllFast
                            End If
                        End If
                    End If
                    Dim ts = DateDiff("n", Commend_Date & " " & Commend_time, dr.Item("LU_StopActual_Date").ToString.Substring(0, 2) & " " & GetNameMonth(dr.Item("LU_StopActual_Date").ToString.Substring(3, 2)) & " 20" & dr.Item("LU_StopActual_Date").ToString.Substring(6, 2) & " " & dr.Item("LU_StopActual_Time"))


                    Dim Hour As String = "0"
                    Dim chksub As String = ""
                    Dim Minute As String = "0"
                    If (LY >= (ts \ 60)) Then
                        If (LY = (ts \ 60)) Then
                            If (ts Mod 60) > 0 Then
                                chksub = "-"
                                Hour = ((ts \ 60) - LY)
                                Minute = (ts Mod 60)
                            Else
                                If (ts Mod 60) > 0 Then
                                    Minute = 60 - (ts Mod 60)
                                    LY -= 1
                                End If
                                Hour = LY - (ts \ 60)
                            End If
                        Else
                            If (ts Mod 60) > 0 Then
                                Minute = 60 - (ts Mod 60)
                                LY -= 1
                            End If
                            Hour = LY - (ts \ 60)
                        End If
                    Else
                        chksub = "-"
                        Hour = ((ts \ 60) - LY)
                        Minute = (ts Mod 60)
                    End If
                    Return chksub & Hour & ":" & CInt(Minute).ToString("00")
                Next
            End If
        Catch ex As Exception

        End Try
        Return ""
    End Function

    Public Function ComputeRemainLaytime(ByVal AllFast_Date As String, ByVal AllFast_Time As String, ByVal LayTIme As String, ByVal DateFrom As String, ByVal DateTo As String, ByVal POB_Date As String, ByVal POB_Time As String, ByVal Dis_Date As String, ByVal Dis_Time As String, ByVal NOR_Date As String, ByVal NOR_TIme As String) As String
        Try
            Dim chksub As String = ""
            If (CDate(NOR_Date) < CDate(DateFrom)) Then
                'เรือมาก่อน
                If (CDate(AllFast_Date) < CDate(DateFrom)) Then
                    'เรือเข้าท่าก่อน Lay Can
                    Dim ts
                    If (Dis_Date = "") Then
                        ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time, Date.Now.ToString("dd MMM", New CultureInfo("en-US")) & " " & Date.Now.ToString("yyyy HH:mm"))
                    Else
                        ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time, Dis_Date.Substring(0, 2) & " " & GetNameMonth(Dis_Date.Substring(3, 2)) & " " & CInt("20" & Dis_Date.Substring(6, 2)) & " " & Dis_Time)
                    End If

                    Dim LY As Integer = CInt(LayTIme)
                    Dim Hour As String = "0"
                    Dim Minute As String = "0"
                    If (LY >= (ts \ 60)) Then
                        If (LY = (ts \ 60)) Then
                            If (ts Mod 60) > 0 Then
                                chksub = "-"
                                Hour = ((ts \ 60) - LY)
                                Minute = (ts Mod 60)
                            Else
                                If (ts Mod 60) > 0 Then
                                    Minute = 60 - (ts Mod 60)
                                    LY -= 1
                                End If
                                Hour = LY - (ts \ 60)
                            End If
                        Else
                            If (ts Mod 60) > 0 Then
                                Minute = 60 - (ts Mod 60)
                                LY -= 1
                            End If
                            Hour = LY - (ts \ 60)
                        End If
                    Else
                        chksub = "-"
                        Hour = ((ts \ 60) - LY)
                        Minute = (ts Mod 60)
                    End If

                    Return chksub & Hour & ":" & CInt(Minute).ToString("00")
                Else
                    'เรือเข้าใน Laycan 
                    If (CDate(POB_Date) = CDate(DateFrom)) Then
                        If (CInt(POB_Time.Split(":")(0)) < 6) Then
                            Dim ts
                            If (Dis_Date = "") Then
                                ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time, Date.Now.ToString("dd MMM", New CultureInfo("en-US")) & " " & Date.Now.ToString("yyyy HH:mm"))
                            Else
                                ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time, Dis_Date.Substring(0, 2) & " " & GetNameMonth(Dis_Date.Substring(3, 2)) & " " & CInt("20" & Dis_Date.Substring(6, 2)) & " " & Dis_Time)
                            End If
                            Dim LY As Integer = CInt(LayTIme)
                            Dim Hour As String = "0"
                            Dim Minute As String = "0"
                            If (LY >= (ts \ 60)) Then
                                If (LY = (ts \ 60)) Then
                                    If (ts Mod 60) > 0 Then
                                        chksub = "-"
                                        Hour = ((ts \ 60) - LY)
                                        Minute = (ts Mod 60)
                                    Else
                                        If (ts Mod 60) > 0 Then
                                            Minute = 60 - (ts Mod 60)
                                            LY -= 1
                                        End If
                                        Hour = LY - (ts \ 60)
                                    End If
                                Else
                                    If (ts Mod 60) > 0 Then
                                        Minute = 60 - (ts Mod 60)
                                        LY -= 1
                                    End If
                                    Hour = LY - (ts \ 60)
                                End If
                            Else
                                chksub = "-"
                                Hour = ((ts \ 60) - LY)
                                Minute = (ts Mod 60)
                            End If
                            Return chksub & Hour & ":" & CInt(Minute).ToString("00")
                        Else
                            Dim ts
                            If (Dis_Date = "") Then
                                ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " 06:00", Date.Now.ToString("dd MMM", New CultureInfo("en-US")) & " " & Date.Now.ToString("yyyy HH:mm"))
                            Else
                                ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " 06:00", Dis_Date.Substring(0, 2) & " " & GetNameMonth(Dis_Date.Substring(3, 2)) & " " & CInt("20" & Dis_Date.Substring(6, 2)) & " " & Dis_Time)
                            End If
                            Dim LY As Integer = CInt(LayTIme)
                            Dim tstemp = DateDiff("n", CDate(POB_Date).ToString("dd MMM") & " " & CInt(POB_Date.Split(" ")(2)) & " " & POB_Time, CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time)
                            ts -= tstemp
                            Dim Hour As String = "0"
                            Dim Minute As String = "0"

                            If (LY >= (ts \ 60)) Then
                                If (LY = (ts \ 60)) Then
                                    If (ts Mod 60) > 0 Then
                                        chksub = "-"
                                        Hour = ((ts \ 60) - LY)
                                        Minute = (ts Mod 60)
                                    Else
                                        If (ts Mod 60) > 0 Then
                                            Minute = 60 - (ts Mod 60)
                                            LY -= 1
                                        End If
                                        Hour = LY - (ts \ 60)
                                    End If
                                Else
                                    If (ts Mod 60) > 0 Then
                                        Minute = 60 - (ts Mod 60)
                                        LY -= 1
                                    End If
                                    Hour = LY - (ts \ 60)
                                End If
                            Else
                                chksub = "-"
                                Hour = ((ts \ 60) - LY)
                                Minute = (ts Mod 60)
                            End If
                            Return chksub & Hour & ":" & CInt(Minute).ToString("00")
                        End If
                    Else
                        Dim ts
                        If (Dis_Date = "") Then
                            ts = DateDiff("n", CDate(DateFrom).ToString("dd MMM") & " " & CInt(DateFrom.Split(" ")(2)) & " 06:00", Date.Now.ToString("dd MMM", New CultureInfo("en-US")) & " " & Date.Now.ToString("yyyy HH:mm"))
                        Else
                            ts = DateDiff("n", CDate(DateFrom).ToString("dd MMM") & " " & CInt(DateFrom.Split(" ")(2)) & " 06:00", Dis_Date.Substring(0, 2) & " " & GetNameMonth(Dis_Date.Substring(3, 2)) & " " & CInt("20" & Dis_Date.Substring(6, 2)) & " " & Dis_Time)
                        End If
                        Dim LY As Integer = CInt(LayTIme)
                        Dim tstemp = DateDiff("n", CDate(POB_Date).ToString("dd MMM") & " " & CInt(POB_Date.Split(" ")(2)) & " " & POB_Time, CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time)
                        ts -= tstemp
                        Dim Hour As String = "0"
                        Dim Minute As String = "0"

                        If (LY >= (ts \ 60)) Then
                            If (LY = (ts \ 60)) Then
                                If (ts Mod 60) > 0 Then
                                    chksub = "-"
                                    Hour = ((ts \ 60) - LY)
                                    Minute = (ts Mod 60)
                                Else
                                    If (ts Mod 60) > 0 Then
                                        Minute = 60 - (ts Mod 60)
                                        LY -= 1
                                    End If
                                    Hour = LY - (ts \ 60)
                                End If
                            Else
                                If (ts Mod 60) > 0 Then
                                    Minute = 60 - (ts Mod 60)
                                    LY -= 1
                                End If
                                Hour = LY - (ts \ 60)
                            End If
                        Else
                            chksub = "-"
                            Hour = ((ts \ 60) - LY)
                            Minute = (ts Mod 60)
                        End If
                        Return chksub & Hour & ":" & CInt(Minute).ToString("00")
                    End If
                End If
            ElseIf (CDate(NOR_Date) = CDate(DateFrom)) Then
                'เรือมาใน Lay Can
                Dim New_AllFast As String = (CInt(NOR_TIme.Split(":")(0)) + 6) & ":" & NOR_TIme.Split(":")(1)
                Dim New_AllFast_Date As String
                If (CInt(New_AllFast.Split(":")(0)) > 23) Then
                    New_AllFast_Date = CDate(NOR_Date.Split(" ")(0) & " " & NOR_Date.Split(" ")(1) & " " & CInt(NOR_Date.Split(" ")(2))).AddDays(1).ToString("dd MMM yyyy", New CultureInfo("en-US"))
                    New_AllFast = (CInt(NOR_TIme.Split(":")(0)) + 6) Mod 24 & ":" & NOR_TIme.Split(":")(1)
                Else
                    New_AllFast_Date = NOR_Date
                End If
                If (CDate(POB_Date) < CDate(New_AllFast_Date)) Then
                    Dim ts
                    If (Dis_Date = "") Then
                        ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time, Date.Now.ToString("dd MMM", New CultureInfo("en-US")) & " " & Date.Now.ToString("yyyy HH:mm"))
                    Else
                        ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time, Dis_Date.Substring(0, 2) & " " & GetNameMonth(Dis_Date.Substring(3, 2)) & " " & CInt("20" & Dis_Date.Substring(6, 2)) & " " & Dis_Time)
                    End If
                    Dim LY As Integer = CInt(LayTIme)
                    'Dim tstemp = DateDiff("n", POB_Date & " " & POB_Time, AllFast_Date & " " & AllFast_Time)
                    'ts -= tstemp
                    Dim Hour As String = "0"
                    Dim Minute As String = "0"
                    If (LY >= (ts \ 60)) Then
                        If (LY = (ts \ 60)) Then
                            If (ts Mod 60) > 0 Then
                                chksub = "-"
                                Hour = ((ts \ 60) - LY)
                                Minute = (ts Mod 60)
                            Else
                                If (ts Mod 60) > 0 Then
                                    Minute = 60 - (ts Mod 60)
                                    LY -= 1
                                End If
                                Hour = LY - (ts \ 60)
                            End If
                        Else
                            If (ts Mod 60) > 0 Then
                                Minute = 60 - (ts Mod 60)
                                LY -= 1
                            End If
                            Hour = LY - (ts \ 60)
                        End If
                    Else
                        chksub = "-"
                        Hour = ((ts \ 60) - LY)
                        Minute = (ts Mod 60)
                    End If
                    Return chksub & Hour & ":" & CInt(Minute).ToString("00")
                ElseIf (CDate(POB_Date) = CDate(New_AllFast_Date)) Then
                    'วันเดียวกัน
                    If (CInt(POB_Time.Split(":")(0)) < CInt(New_AllFast.Split(":")(0))) Then
                        'เรือเข้าก่อน
                        Dim ts
                        If (Dis_Date = "") Then
                            ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time, Date.Now.ToString("dd MMM", New CultureInfo("en-US")) & " " & Date.Now.ToString("yyyy HH:mm"))
                        Else
                            ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time, Dis_Date.Substring(0, 2) & " " & GetNameMonth(Dis_Date.Substring(3, 2)) & " " & CInt("20" & Dis_Date.Substring(6, 2)) & " " & Dis_Time)
                        End If
                        Dim LY As Integer = CInt(LayTIme)

                        Dim Hour As String = "0"
                        Dim Minute As String = "0"
                        If (LY >= (ts \ 60)) Then
                            If (LY = (ts \ 60)) Then
                                If (ts Mod 60) > 0 Then
                                    chksub = "-"
                                    Hour = ((ts \ 60) - LY)
                                    Minute = (ts Mod 60)
                                Else
                                    If (ts Mod 60) > 0 Then
                                        Minute = 60 - (ts Mod 60)
                                        LY -= 1
                                    End If
                                    Hour = LY - (ts \ 60)
                                End If
                            Else
                                If (ts Mod 60) > 0 Then
                                    Minute = 60 - (ts Mod 60)
                                    LY -= 1
                                End If
                                Hour = LY - (ts \ 60)
                            End If
                        Else
                            chksub = "-"
                            Hour = ((ts \ 60) - LY)
                            Minute = (ts Mod 60)
                        End If
                        Return chksub & Hour & ":" & CInt(Minute).ToString("00")
                    ElseIf (CInt(POB_Time.Split(":")(0)) = CInt(New_AllFast.Split(":")(0))) Then
                        If (CInt(POB_Time.Split(":")(1)) <= CInt(New_AllFast.Split(":")(1))) Then
                            'เรือเข้าก่อน
                            Dim ts
                            If (Dis_Date = "") Then
                                ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time, Date.Now.ToString("dd MMM", New CultureInfo("en-US")) & " " & Date.Now.ToString("yyyy HH:mm"))
                            Else
                                ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time, Dis_Date.Substring(0, 2) & " " & GetNameMonth(Dis_Date.Substring(3, 2)) & " " & CInt("20" & Dis_Date.Substring(6, 2)) & " " & Dis_Time)
                            End If
                            Dim LY As Integer = CInt(LayTIme)
                            Dim Hour As String = "0"
                            Dim Minute As String = "0"
                            If (LY >= (ts \ 60)) Then
                                If (LY = (ts \ 60)) Then
                                    If (ts Mod 60) > 0 Then
                                        chksub = "-"
                                        Hour = ((ts \ 60) - LY)
                                        Minute = (ts Mod 60)
                                    Else
                                        If (ts Mod 60) > 0 Then
                                            Minute = 60 - (ts Mod 60)
                                            LY -= 1
                                        End If
                                        Hour = LY - (ts \ 60)
                                    End If
                                Else
                                    If (ts Mod 60) > 0 Then
                                        Minute = 60 - (ts Mod 60)
                                        LY -= 1
                                    End If
                                    Hour = LY - (ts \ 60)
                                End If
                            Else
                                chksub = "-"
                                Hour = ((ts \ 60) - LY)
                                Minute = (ts Mod 60)
                            End If
                            Return chksub & Hour & ":" & CInt(Minute).ToString("00")
                        Else
                            'เรือเข้าหลัง
                            Dim ts
                            If (Dis_Date = "") Then
                                ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & New_AllFast, Date.Now.ToString("dd MMM", New CultureInfo("en-US")) & " " & Date.Now.ToString("yyyy HH:mm"))
                            Else
                                ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & New_AllFast, Dis_Date.Substring(0, 2) & " " & GetNameMonth(Dis_Date.Substring(3, 2)) & " " & CInt("20" & Dis_Date.Substring(6, 2)) & " " & Dis_Time)
                            End If
                            Dim LY As Integer = CInt(LayTIme)
                            ' Dim tstemp = DateDiff("n", CDate(POB_Date).ToString("dd MMM") & " " & CInt(POB_Date.Split(" ")(2))  & " " & POB_Time, CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2))  & " " & AllFast_Time)
                            '  ts -= tstemp

                            Dim Hour As String = "0"
                            Dim Minute As String = "0"

                            If (LY >= (ts \ 60)) Then
                                If (LY = (ts \ 60)) Then
                                    If (ts Mod 60) > 0 Then
                                        chksub = "-"
                                        Hour = ((ts \ 60) - LY)
                                        Minute = (ts Mod 60)
                                    Else
                                        If (ts Mod 60) > 0 Then
                                            Minute = 60 - (ts Mod 60)
                                            LY -= 1
                                        End If
                                        Hour = LY - (ts \ 60)
                                    End If
                                Else
                                    If (ts Mod 60) > 0 Then
                                        Minute = 60 - (ts Mod 60)
                                        LY -= 1
                                    End If
                                    Hour = LY - (ts \ 60)
                                End If
                            Else
                                chksub = "-"
                                Hour = ((ts \ 60) - LY)
                                Minute = (ts Mod 60)
                            End If
                            Return chksub & Hour & ":" & CInt(Minute).ToString("00")
                        End If
                    Else
                        'เรือเข้าหลัง 
                        Dim ts
                        If (Dis_Date = "") Then
                            ts = DateDiff("n", CDate(New_AllFast_Date).ToString("dd MMM") & " " & CInt(New_AllFast_Date.Split(" ")(2)) & " " & New_AllFast, Date.Now.ToString("dd MMM", New CultureInfo("en-US")) & " " & Date.Now.ToString("yyyy HH:mm"))
                        Else
                            ts = DateDiff("n", CDate(New_AllFast_Date).ToString("dd MMM") & " " & CInt(New_AllFast_Date.Split(" ")(2)) & " " & New_AllFast, Dis_Date.Substring(0, 2) & " " & GetNameMonth(Dis_Date.Substring(3, 2)) & " " & CInt("20" & Dis_Date.Substring(6, 2)) & " " & Dis_Time)
                        End If
                        Dim LY As Integer = CInt(LayTIme)
                        Dim tstemp = DateDiff("n", CDate(POB_Date).ToString("dd MMM") & " " & CInt(POB_Date.Split(" ")(2)) & " " & POB_Time, CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time)
                        ts -= tstemp

                        Dim Hour As String = "0"
                        Dim Minute As String = "0"

                        If (LY >= (ts \ 60)) Then
                            If (LY = (ts \ 60)) Then
                                If (ts Mod 60) > 0 Then
                                    chksub = "-"
                                    Hour = ((ts \ 60) - LY)
                                    Minute = (ts Mod 60)
                                Else
                                    If (ts Mod 60) > 0 Then
                                        Minute = 60 - (ts Mod 60)
                                        LY -= 1
                                    End If
                                    Hour = LY - (ts \ 60)
                                End If
                            Else
                                If (ts Mod 60) > 0 Then
                                    Minute = 60 - (ts Mod 60)
                                    LY -= 1
                                End If
                                Hour = LY - (ts \ 60)
                            End If
                        Else
                            chksub = "-"
                            Hour = ((ts \ 60) - LY)
                            Minute = (ts Mod 60)
                        End If
                        Return chksub & Hour & ":" & CInt(Minute).ToString("00")
                    End If
                Else
                    '
                    Dim ts
                    If (Dis_Date = "") Then
                        ts = DateDiff("n", CDate(New_AllFast_Date).ToString("dd MMM") & " " & CInt(New_AllFast_Date.Split(" ")(2)) & " " & New_AllFast, Date.Now.ToString("dd MMM", New CultureInfo("en-US")) & " " & Date.Now.ToString("yyyy HH:mm"))
                    Else
                        ts = DateDiff("n", CDate(New_AllFast_Date).ToString("dd MMM") & " " & CInt(New_AllFast_Date.Split(" ")(2)) & " " & New_AllFast, Dis_Date.Substring(0, 2) & " " & GetNameMonth(Dis_Date.Substring(3, 2)) & " " & CInt("20" & Dis_Date.Substring(6, 2)) & " " & Dis_Time)
                    End If
                    Dim LY As Integer = CInt(LayTIme)
                    Dim tstemp = DateDiff("n", CDate(POB_Date).ToString("dd MMM") & " " & CInt(POB_Date.Split(" ")(2)) & " " & POB_Time, CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time)
                    ts -= tstemp
                    Dim Hour As String = "0"
                    Dim Minute As String = "0"

                    If (LY >= (ts \ 60)) Then
                        If (LY = (ts \ 60)) Then
                            If (ts Mod 60) > 0 Then
                                chksub = "-"
                                Hour = ((ts \ 60) - LY)
                                Minute = (ts Mod 60)
                            Else
                                If (ts Mod 60) > 0 Then
                                    Minute = 60 - (ts Mod 60)
                                    LY -= 1
                                End If
                                Hour = LY - (ts \ 60)
                            End If
                        Else
                            If (ts Mod 60) > 0 Then
                                Minute = 60 - (ts Mod 60)
                                LY -= 1
                            End If
                            Hour = LY - (ts \ 60)
                        End If
                    Else
                        chksub = "-"
                        Hour = ((ts \ 60) - LY)
                        Minute = (ts Mod 60)
                    End If
                    Return chksub & Hour & ":" & CInt(Minute).ToString("00")
                End If

            Else
                If (CDate(NOR_Date) <= CDate(DateTo)) Then
                    'เรือมาใน Lay Can
                    Dim New_AllFast As String = (CInt(NOR_TIme.Split(":")(0)) + 6) & ":" & NOR_TIme.Split(":")(1)
                    Dim New_AllFast_Date As String
                    If (CInt(New_AllFast.Split(":")(0)) > 23) Then
                        New_AllFast_Date = CDate(NOR_Date.Split(" ")(0) & " " & NOR_Date.Split(" ")(1) & " " & CInt(NOR_Date.Split(" ")(2))).AddDays(1).ToString("dd MMM yyyy", New CultureInfo("en-US"))
                        New_AllFast = (CInt(NOR_TIme.Split(":")(0)) + 6) Mod 24 & ":" & NOR_TIme.Split(":")(1)
                    Else
                        New_AllFast_Date = NOR_Date
                    End If
                    If (POB_Date <> "") And (POB_Time <> "") Then
                        If (CDate(POB_Date) = CDate(New_AllFast_Date)) Then
                            If (CInt(New_AllFast.Split(":")(0)) > CInt(POB_Time.Split(":")(0))) Then
                                New_AllFast = POB_Time
                            ElseIf (CInt(New_AllFast.Split(":")(0)) = CInt(POB_Time.Split(":")(0))) Then
                                If (CInt(New_AllFast.Split(":")(1)) > CInt(POB_Time.Split(":")(1))) Then
                                    New_AllFast = POB_Time
                                End If
                            End If
                        ElseIf (CDate(POB_Date) < CDate(New_AllFast)) Then
                            New_AllFast_Date = POB_Date
                            New_AllFast = POB_Time
                        End If
                    End If
                    If (CDate(AllFast_Date) < CDate(New_AllFast_Date)) Then
                        Dim ts
                        If (Dis_Date = "") Then
                            ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time, Date.Now.ToString("dd MMM", New CultureInfo("en-US")) & " " & Date.Now.ToString("yyyy HH:mm"))
                        Else
                            ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time, Dis_Date.Substring(0, 2) & " " & GetNameMonth(Dis_Date.Substring(3, 2)) & " " & CInt("20" & Dis_Date.Substring(6, 2)) & " " & Dis_Time)
                        End If
                        Dim LY As Integer = CInt(LayTIme)
                        Dim Hour As String = "0"
                        Dim Minute As String = "0"
                        If (LY >= (ts \ 60)) Then
                            If (LY = (ts \ 60)) Then
                                If (ts Mod 60) > 0 Then
                                    chksub = "-"
                                    Hour = ((ts \ 60) - LY)
                                    Minute = (ts Mod 60)
                                Else
                                    If (ts Mod 60) > 0 Then
                                        Minute = 60 - (ts Mod 60)
                                        LY -= 1
                                    End If
                                    Hour = LY - (ts \ 60)
                                End If
                            Else
                                If (ts Mod 60) > 0 Then
                                    Minute = 60 - (ts Mod 60)
                                    LY -= 1
                                End If
                                Hour = LY - (ts \ 60)
                            End If
                        Else
                            chksub = "-"
                            Hour = ((ts \ 60) - LY)
                            Minute = (ts Mod 60)
                        End If
                        Return chksub & Hour & ":" & CInt(Minute).ToString("00")
                    ElseIf (CDate(AllFast_Date) = CDate(New_AllFast_Date)) Then
                        'วันเดียวกัน
                        If (CInt(AllFast_Time.Split(":")(0)) < CInt(New_AllFast.Split(":")(0))) Then
                            'เรือเข้าก่อน
                            Dim ts
                            If (Dis_Date = "") Then
                                ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time, Date.Now.ToString("dd MMM", New CultureInfo("en-US")) & " " & Date.Now.ToString("yyyy HH:mm"))
                            Else
                                ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time, Dis_Date.Substring(0, 2) & " " & GetNameMonth(Dis_Date.Substring(3, 2)) & " " & CInt("20" & Dis_Date.Substring(6, 2)) & " " & Dis_Time)
                            End If
                            Dim LY As Integer = CInt(LayTIme)
                            Dim Hour As String = "0"
                            Dim Minute As String = "0"
                            If (LY >= (ts \ 60)) Then
                                If (LY = (ts \ 60)) Then
                                    If (ts Mod 60) > 0 Then
                                        chksub = "-"
                                        Hour = ((ts \ 60) - LY)
                                        Minute = (ts Mod 60)
                                    Else
                                        If (ts Mod 60) > 0 Then
                                            Minute = 60 - (ts Mod 60)
                                            LY -= 1
                                        End If
                                        Hour = LY - (ts \ 60)
                                    End If
                                Else
                                    If (ts Mod 60) > 0 Then
                                        Minute = 60 - (ts Mod 60)
                                        LY -= 1
                                    End If
                                    Hour = LY - (ts \ 60)
                                End If
                            Else
                                chksub = "-"
                                Hour = ((ts \ 60) - LY)
                                Minute = (ts Mod 60)
                            End If
                            Return chksub & Hour & ":" & CInt(Minute).ToString("00")
                        ElseIf (CInt(AllFast_Time.Split(":")(0)) = CInt(New_AllFast.Split(":")(0))) Then
                            If (CInt(AllFast_Time.Split(":")(1)) <= CInt(New_AllFast.Split(":")(1))) Then
                                'เรือเข้าก่อน
                                Dim ts
                                If (Dis_Date = "") Then
                                    ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time, Date.Now.ToString("dd MMM", New CultureInfo("en-US")) & " " & Date.Now.ToString("yyyy HH:mm"))
                                Else
                                    ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time, Dis_Date.Substring(0, 2) & " " & GetNameMonth(Dis_Date.Substring(3, 2)) & " " & CInt("20" & Dis_Date.Substring(6, 2)) & " " & Dis_Time)
                                End If
                                Dim LY As Integer = CInt(LayTIme)
                                Dim Hour As String = "0"
                                Dim Minute As String = "0"
                                If (LY >= (ts \ 60)) Then
                                    If (LY = (ts \ 60)) Then
                                        If (ts Mod 60) > 0 Then
                                            chksub = "-"
                                            Hour = ((ts \ 60) - LY)
                                            Minute = (ts Mod 60)
                                        Else
                                            If (ts Mod 60) > 0 Then
                                                Minute = 60 - (ts Mod 60)
                                                LY -= 1
                                            End If
                                            Hour = LY - (ts \ 60)
                                        End If
                                    Else
                                        If (ts Mod 60) > 0 Then
                                            Minute = 60 - (ts Mod 60)
                                            LY -= 1
                                        End If
                                        Hour = LY - (ts \ 60)
                                    End If
                                Else
                                    chksub = "-"
                                    Hour = ((ts \ 60) - LY)
                                    Minute = (ts Mod 60)
                                End If
                                Return chksub & Hour & ":" & CInt(Minute).ToString("00")
                            Else
                                'ใช้เวลาของ Berth to
                                Dim ts
                                If (Dis_Date = "") Then
                                    ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time, Date.Now.ToString("dd MMM", New CultureInfo("en-US")) & " " & Date.Now.ToString("yyyy HH:mm"))
                                Else
                                    ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time, Dis_Date.Substring(0, 2) & " " & GetNameMonth(Dis_Date.Substring(3, 2)) & " " & CInt("20" & Dis_Date.Substring(6, 2)) & " " & Dis_Time)
                                End If
                                Dim LY As Integer = CInt(LayTIme)
                                ' Dim tstemp = DateDiff("n", CDate(POB_Date).ToString("dd MMM") & " " & CInt(POB_Date.Split(" ")(2))  & " " & POB_Time, CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2))  & " " & AllFast_Time)
                                ' ts -= tstemp
                                Dim Hour As String = "0"
                                Dim Minute As String = "0"

                                If (LY >= (ts \ 60)) Then
                                    If (LY = (ts \ 60)) Then
                                        If (ts Mod 60) > 0 Then
                                            chksub = "-"
                                            Hour = ((ts \ 60) - LY)
                                            Minute = (ts Mod 60)
                                        Else
                                            If (ts Mod 60) > 0 Then
                                                Minute = 60 - (ts Mod 60)
                                                LY -= 1
                                            End If
                                            Hour = LY - (ts \ 60)
                                        End If
                                    Else
                                        If (ts Mod 60) > 0 Then
                                            Minute = 60 - (ts Mod 60)
                                            LY -= 1
                                        End If
                                        Hour = LY - (ts \ 60)
                                    End If
                                Else
                                    chksub = "-"
                                    Hour = ((ts \ 60) - LY)
                                    Minute = (ts Mod 60)
                                End If
                                Return chksub & Hour & ":" & CInt(Minute).ToString("00")
                            End If
                        Else
                            'เรือเข้าหลัง 
                            Dim ts
                            If (Dis_Date = "") Then
                                ts = DateDiff("n", CDate(New_AllFast_Date).ToString("dd MMM") & " " & CInt(New_AllFast_Date.Split(" ")(2)) & " " & New_AllFast, Date.Now.ToString("dd MMM", New CultureInfo("en-US")) & " " & Date.Now.ToString("yyyy HH:mm"))
                            Else
                                ts = DateDiff("n", CDate(New_AllFast_Date).ToString("dd MMM") & " " & CInt(New_AllFast_Date.Split(" ")(2)) & " " & New_AllFast, Dis_Date.Substring(0, 2) & " " & GetNameMonth(Dis_Date.Substring(3, 2)) & " " & CInt("20" & Dis_Date.Substring(6, 2)) & " " & Dis_Time)
                            End If
                            Dim LY As Integer = CInt(LayTIme)
                            Dim tstemp = DateDiff("n", CDate(POB_Date).ToString("dd MMM") & " " & CInt(POB_Date.Split(" ")(2)) & " " & POB_Time, CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time)
                            ts -= tstemp
                            Dim Hour As String = "0"
                            Dim Minute As String = "0"

                            If (LY >= (ts \ 60)) Then
                                If (LY = (ts \ 60)) Then
                                    If (ts Mod 60) > 0 Then
                                        chksub = "-"
                                        Hour = ((ts \ 60) - LY)
                                        Minute = (ts Mod 60)
                                    Else
                                        If (ts Mod 60) > 0 Then
                                            Minute = 60 - (ts Mod 60)
                                            LY -= 1
                                        End If
                                        Hour = LY - (ts \ 60)
                                    End If
                                Else
                                    If (ts Mod 60) > 0 Then
                                        Minute = 60 - (ts Mod 60)
                                        LY -= 1
                                    End If
                                    Hour = LY - (ts \ 60)
                                End If
                            Else
                                chksub = "-"
                                Hour = ((ts \ 60) - LY)
                                Minute = (ts Mod 60)
                            End If
                            Return chksub & Hour & ":" & CInt(Minute).ToString("00")
                        End If
                    Else
                        '
                        Dim ts
                        If (Dis_Date = "") Then
                            ts = DateDiff("n", CDate(New_AllFast_Date).ToString("dd MMM") & " " & CInt(New_AllFast_Date.Split(" ")(2)) & " " & New_AllFast, Date.Now.ToString("dd MMM", New CultureInfo("en-US")) & " " & Date.Now.ToString("yyyy HH:mm"))
                        Else
                            ts = DateDiff("n", CDate(New_AllFast_Date).ToString("dd MMM") & " " & CInt(New_AllFast_Date.Split(" ")(2)) & " " & New_AllFast, Dis_Date.Substring(0, 2) & " " & GetNameMonth(Dis_Date.Substring(3, 2)) & " " & CInt("20" & Dis_Date.Substring(6, 2)) & " " & Dis_Time)
                        End If
                        Dim LY As Integer = CInt(LayTIme)
                        Dim tstemp = DateDiff("n", CDate(POB_Date).ToString("dd MMM") & " " & CInt(POB_Date.Split(" ")(2)) & " " & POB_Time, CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time)
                        ts -= tstemp
                        Dim Hour As String = "0"
                        Dim Minute As String = "0"

                        If (LY >= (ts \ 60)) Then
                            If (LY = (ts \ 60)) Then
                                If (ts Mod 60) > 0 Then
                                    chksub = "-"
                                    Hour = ((ts \ 60) - LY)
                                    Minute = (ts Mod 60)
                                Else
                                    If (ts Mod 60) > 0 Then
                                        Minute = 60 - (ts Mod 60)
                                        LY -= 1
                                    End If
                                    Hour = LY - (ts \ 60)
                                End If
                            Else
                                If (ts Mod 60) > 0 Then
                                    Minute = 60 - (ts Mod 60)
                                    LY -= 1
                                End If
                                Hour = LY - (ts \ 60)
                            End If
                        Else
                            chksub = "-"
                            Hour = ((ts \ 60) - LY)
                            Minute = (ts Mod 60)
                        End If
                        Return chksub & Hour & ":" & CInt(Minute).ToString("00")
                    End If
                Else
                    'เรือมาหลัง Lay Can
                    Dim ts
                    If (Dis_Date = "") Then
                        ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time, Date.Now.ToString("dd MMM", New CultureInfo("en-US")) & " " & Date.Now.ToString("yyyy HH:mm"))
                    Else
                        ts = DateDiff("n", CDate(AllFast_Date).ToString("dd MMM") & " " & CInt(AllFast_Date.Split(" ")(2)) & " " & AllFast_Time, Dis_Date.Substring(0, 2) & " " & GetNameMonth(Dis_Date.Substring(3, 2)) & " " & CInt("20" & Dis_Date.Substring(6, 2)) & " " & Dis_Time)
                    End If
                    Dim LY As Integer = CInt(LayTIme)
                    Dim Hour As String = "0"
                    Dim Minute As String = "0"
                    If (LY >= (ts \ 60)) Then
                        If (LY = (ts \ 60)) Then
                            If (ts Mod 60) > 0 Then
                                chksub = "-"
                                Hour = ((ts \ 60) - LY)
                                Minute = (ts Mod 60)
                            Else
                                If (ts Mod 60) > 0 Then
                                    Minute = 60 - (ts Mod 60)
                                    LY -= 1
                                End If
                                Hour = LY - (ts \ 60)
                            End If
                        Else
                            If (ts Mod 60) > 0 Then
                                Minute = 60 - (ts Mod 60)
                                LY -= 1
                            End If
                            Hour = LY - (ts \ 60)
                        End If
                    Else
                        chksub = "-"
                        Hour = ((ts \ 60) - LY)
                        Minute = (ts Mod 60)
                    End If
                    Return chksub & Hour & ":" & CInt(Minute).ToString("00")
                End If
            End If
        Catch ex As Exception
            Return ""
        End Try
    End Function

    Private Function GetNameMonth(ByVal Month As String) As String
        Try
            Select Case (CInt(Month).ToString("00"))
                Case "01"
                    Return "Jan"
                Case "02"
                    Return "Feb"
                Case "03"
                    Return "Mar"
                Case "04"
                    Return "Apr"
                Case "05"
                    Return "May"
                Case "06"
                    Return "Jun"
                Case "07"
                    Return "Jul"
                Case "08"
                    Return "Aug"
                Case "09"
                    Return "Sep"
                Case "10"
                    Return "Oct"
                Case "11"
                    Return "Nov"
                Case "12"
                    Return "Dec"
                Case Else
                    Return ""
            End Select
        Catch ex As Exception
            Return Month
        End Try

    End Function

    Public Function GetMaster_Page(ByVal User_Name As String, ByVal EventUser As String) As String
        If (EventUser = "M") Then 'หน้าหลัก
            Select Case (Get_MASTERUser(User_Name))
                Case "B" 'Orange
                    Return "~/Master_Page/Master_Page_blue.Master"
                Case "O" 'blue
                    Return "~/Master_Page/Master_Page_orange.Master"
                Case "V" 'Violet
                    Return "~/Master_Page/Master_Page_violet.Master"
                Case "E"
                    Return "~/Master_Page/Master_Page_eco.Master"
                Case Else
                    Return "~/Master_Page/Master_IPORT.Master"
            End Select
        Else 'Popup
            Select Case (Get_MASTERUser(User_Name))
                Case "B" 'Orange
                    Return "~/Master_Page/Master_Popup_blue.Master"
                Case "O" 'blue
                    Return "~/Master_Page/Master_Popup_orange.Master"
                Case "V" 'Violet
                    Return "~/Master_Page/Master_Popup_violet.Master"
                Case "E" 'eco
                    Return "~/Master_Page/Master_Popup_eco.Master"
                Case Else
                    Return "~/Master_Page/Master_Popup.Master"
            End Select
        End If

    End Function

    Public Function Get_MASTERUser(ByVal User_Name As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT  Master_P "
        sql &= " FROM  UserIPORT"
        sql &= " Where User_name = @User_Name"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@User_Name", SqlDbType.NVarChar, 50).Value = User_Name
        Try
            adap.Fill(ds, "BookingTariff")
            adap = Nothing
            con.Close()
            If (ds.Tables(0).Rows.Count = 1) Then
                Return ds.Tables(0).Rows(0).Item("Master_P") & ""
            Else
                Return ""
            End If
        Catch ex As Exception
            Return ""
        End Try

    End Function

    Public Function Get_Tracking(ByVal MAGent_no As String, ByVal booking_no As String, ByVal Vessel As String, ByVal DateFrom As String, ByVal DateTo As String, ByVal Booking_Code As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT Booking_no,Vehicle_Text,Date_From,Date_To,Status,MAgent_no,PORT " & vbCrLf
        sql &= " From Booking where booking_no <> '' " & vbCrLf
        If (MAGent_no <> "") Then
            If (Booking_Code = "") Then
                sql &= " AND MAgent_no = @MAgent_no "
            Else
                sql &= " AND (MAgent_no = @MAgent_no OR Booking_Code = @Booking_Code) "
            End If
        ElseIf (Booking_Code <> "") Then
            sql &= " AND (Booking_Code = @Booking_Code) "
        End If
        If (booking_no <> "") Then
            sql &= " AND Booking_no Like '%' + @Booking_no + '%' "
        End If
        If (Vessel <> "") Then
            sql &= " AND Vehicle_Text Like '%' + @Vehicle_Text + '%' "
        End If
        If (DateFrom <> "") And (DateTo <> "") Then
            sql &= " And  (CONVERT(VARCHAR(10), Convert(DateTime,Date_From) , 112) Between " & DateFrom & " and " & DateTo & " "
            sql &= " OR  CONVERT(VARCHAR(10), Convert(DateTime,Date_To) , 112) Between " & DateFrom & " and " & DateTo & " )"
        End If
        sql &= " order by Booking_no Desc " & vbCrLf
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = booking_no
        adap.SelectCommand.Parameters.Add("@MAgent_no", SqlDbType.NVarChar, 10).Value = MAGent_no
        adap.SelectCommand.Parameters.Add("@Booking_Code", SqlDbType.NVarChar, 50).Value = Booking_Code
        adap.SelectCommand.Parameters.Add("@Vehicle_Text", SqlDbType.NVarChar, 200).Value = Vessel
        adap.Fill(ds, "Tracking")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_PreBookingOneRecord(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT * " & vbCrLf
        sql &= " From Pre_Booking " & vbCrLf
        sql &= " WHERE Booking_no = @Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "Pre")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_SignalOUT(ByVal Booking_no As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select A_By , A_Date , A_TIme from Action_Operation A,User_Role U " & vbCrLf
        sql &= " where A.A_Username = U.User_name " & vbCrLf
        sql &= " and U.Role_no = '00012' "
        sql &= " and A.Booking_no = @Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "Pre")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_PreBooking(ByVal MAGent_no As String, ByVal booking_no As String, ByVal Vessel As String, ByVal DateFrom As String, ByVal DateTo As String, ByVal Booking_Code As String, ByVal Status As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select Booking_no, Booking_Code, Booking_name, MAgent_no, MAgent_name, Vehicle_no, Vehicle_Text, Vehicle_LOA, Vehicle_DWT, Vehicle_Draft, Vehicle_GRT,  " & vbCrLf
        sql &= "    Vehicle_CNTRY, Date_From, Date_To, PORT, Vehicle_Destination, Vehicle_TypeBuy, Water_Point, Electricity, Status, Approve " & vbCrLf
        sql &= " from Pre_Booking " & vbCrLf
        sql &= " where Pre_Booking.Booking_no <> '' "
        If (MAGent_no <> "") Then
            If (Booking_Code = "") Then
                sql &= " and MAgent_no = @MAgent_no "
            Else
                sql &= " and (MAgent_no = @MAgent_no OR Booking_Code = @Booking_Code  )"
            End If
        ElseIf (Booking_Code <> "") Then
            sql &= " and (Booking_Code = @Booking_Code)"
        End If
        If (booking_no <> "") Then
            sql &= " AND Booking_no Like '%' + @Booking_no + '%' "
        End If
        If (Vessel <> "") Then
            sql &= " AND Vehicle_Text Like '%' + @Vehicle_Text + '%' "
        End If
        If (DateFrom <> "") And (DateTo <> "") Then
            sql &= " And  (CONVERT(VARCHAR(10), Convert(DateTime,Date_From) , 112) Between " & DateFrom & " and " & DateTo & " "
            sql &= " OR  CONVERT(VARCHAR(10), Convert(DateTime,Date_To) , 112) Between " & DateFrom & " and " & DateTo & " )"
        End If
        sql &= " Union " & vbCrLf
        sql &= " select  Booking_no, Booking_Code, Booking_name, MAgent_no, MAgent_name, Vehicle_no, Vehicle_Text, Vehicle_LOA, Vehicle_DWT, Vehicle_Draft, Vehicle_GRT,  " & vbCrLf
        sql &= "  Vehicle_CNTRY, Date_From, Date_To, PORT, Vehicle_Destination, Vehicle_TypeBuy, Water_Point, Electricity, Status, '' as Approve " & vbCrLf
        sql &= " from Booking " & vbCrLf
        sql &= " where Booking_no Not in (select Booking_no from Pre_Booking) " & vbCr
        If (MAGent_no <> "") Then
            If (Booking_Code = "") Then
                sql &= " and MAgent_no = @MAgent_no "
            Else
                sql &= " and (MAgent_no = @MAgent_no OR Booking_Code = @Booking_Code  )"
            End If
        ElseIf (Booking_Code <> "") Then
            sql &= " and (Booking_Code = @Booking_Code)"
        End If
        If (booking_no <> "") Then
            sql &= " AND Booking_no Like '%' + @Booking_no + '%' "
        End If
        If (Vessel <> "") Then
            sql &= " AND Vehicle_Text Like '%' + @Vehicle_Text + '%' "
        End If
        If (Status <> "") Then
            sql &= " and Status not in (" & Status & ") "
        End If
        If (DateFrom <> "") And (DateTo <> "") Then
            sql &= " And  (CONVERT(VARCHAR(10), Convert(DateTime,Date_From) , 112) Between " & DateFrom & " and " & DateTo & " "
            sql &= " OR  CONVERT(VARCHAR(10), Convert(DateTime,Date_To) , 112) Between " & DateFrom & " and " & DateTo & " )"
        End If
        sql &= " order by Booking_no Desc " & vbCrLf
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@MAgent_no", SqlDbType.NVarChar, 10).Value = MAGent_no
        adap.SelectCommand.Parameters.Add("@Booking_Code", SqlDbType.NVarChar, 50).Value = Booking_Code
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = booking_no
        adap.SelectCommand.Parameters.Add("@Vehicle_Text", SqlDbType.NVarChar, 200).Value = Vessel
        adap.Fill(ds, "MyBooking")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_MshipBOoking(ByVal DateFrom As String, ByVal Date_To As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "select Booking_no from Booking_LogSheet_Out " & vbCrLf
        sql &= " Where Last_Date <> '' " & vbCrLf
        sql &= " And  CONVERT(VARCHAR(10), Convert(DateTime,Last_Date) , 112) Between " & DateFrom & " and " & Date_To & " "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.Fill(ds, "MyBooking")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_MshipLiquid(ByVal DateFrom As String, ByVal Date_To As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        'sql = " select Booking.Booking_no,Sum(Convert(float,LU_BLMT + '')) as LU_BLMT,Sum(Convert(float, LU_BL + '')) as LU_BL,Vehicle_GRT from Load_Unload_Product,Booking " & vbCrLf
        'sql &= " where Booking.Booking_no in ( " & vbCrLf
        'sql &= "select Booking_no from Load_Unload_Vessel " & vbCrLf
        'sql &= " Where Depart_Actual_Date <> '' " & vbCrLf
        'sql &= " And '20' + subString(Depart_Actual_Date,7,2) + subString(Depart_Actual_Date,4,2) + subString(Depart_Actual_Date,1,2) Between " & DateFrom & " and " & Date_To & " "
        'sql &= " ) "
        'sql &= " and Booking.Booking_no =  Load_Unload_Product.Booking_no " & vbCrLf
        'sql &= " and LU_BLMT <> '' " & vbCrLf
        'sql &= " Group By Booking.Booking_no,Vehicle_GRT " & vbCrLf

        sql = "select V.Booking_no,LU_BL,LU_BLMT from Load_Unload_Vessel V,Load_Unload_Product P,Booking B " & vbCrLf
        sql &= " Where Depart_Actual_Date <> '' " & vbCrLf
        sql &= " And '20' + subString(Depart_Actual_Date,7,2) + subString(Depart_Actual_Date,4,2) + subString(Depart_Actual_Date,1,2) Between " & DateFrom & " and " & Date_To & " "
        sql &= " and V.Booking_no = P.Booking_no " & vbCrLf
        sql &= " and V.Booking_no Not in (select Shift_Booking as Booking_no from BookingWharf where Shift_Booking <> '')"
        sql &= " and V.Booking_no = B.Booking_no "
        sql &= " and B.Status = 'FN'"
        sql &= " and B.PORT = 'L'"
        sql &= " Order by V.Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.Fill(ds, "MyBooking")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Total_Ship(ByVal Port As String, ByVal DateFrom As String, ByVal Date_To As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select Booking.Booking_no,Booking.Vehicle_Text as Vessel,MAgent_name as Agent,Booking.Vehicle_LOA as Feet,Vessel.VEH_LENGTH as M " & vbCrLf
        sql &= " ,Booking.Vehicle_GRT as GRT,Booking.Vehicle_DWT as DWT,Booking.Vehicle_CNTRY as Flag " & vbCrLf
        sql &= " from Booking,Vessel,Load_Unload_Vessel " & vbCrLf
        sql &= " where Booking.Status = 'FN' And Booking.Booking_no = Load_Unload_Vessel.Booking_no AND  Booking.Vehicle_no = Vessel.Vehicle " & vbCrLf
        sql &= " and PORT = '" & Port & "' "
        sql &= " And '20' + subString(Depart_Actual_Date,7,2) + subString(Depart_Actual_Date,4,2) + subString(Depart_Actual_Date,1,2) Between " & DateFrom & " and " & Date_To & " "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.Fill(ds, "Total")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_Payment_Report(ByVal DateFrom As String, ByVal Date_To As String, ByVal Customer As String, ByVal Bank As String, ByVal TypeP As String, ByVal Type_Pay As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        If (TypeP = "0") Then
            sql = "select Distinct Convert(VARCHAR,Convert(DATETIME,A.Value_Date),106) as Value_Date,M.Customer as Customer_Code,C.NAME1 + C.NAME2 + C.NAME3 as Customer_Name,A.Ex_belnr as RV_no,A.Item_Text + ' ' + A.Gl_Account as BANK " & vbCrLf
            sql &= " , A.Amount,A.WHT,'DY' as  Type " & vbCrLf
            sql &= " from SAP_Advanced_Payment A,SAP_Advanced_Payment_Material M,Customer C " & vbCrLf
            sql &= " where A.Iport_no = M.Iport_no " & vbCrLf
            sql &= " and '0000' + M.Customer = C.Customer_no " & vbCrLf
            sql &= " And A.Value_Date  Between " & DateFrom & " and " & Date_To & " " & vbCrLf
            If (Customer <> "") Then
                sql &= " AND C.Customer_no Like '%' + @Customer + '%'  " & vbCrLf
            End If
            If (Bank <> "0") Then
                sql &= " AND A.Item_Text Like '%' + @Bank + '%' " & vbCrLf
            End If
            If (Type_Pay <> "2") Then
                sql &= " AND A.Type_Pay = @Type_Pay " & vbCrLf
            End If
        ElseIf (TypeP = "1") Then
            sql = " select Distinct Convert(VARCHAR,Convert(DATETIME,S.Value_Date),106) as Value_Date,S.IM_CUSTOMER as Customer_Code,C.NAME1 + C.NAME2 + C.NAME3 as Customer_Name,S.Ex_belnr as RV_no,S.Item_Text + ' ' + S.Gl_Account as BANK " & vbCrLf
            sql &= "  ,S.Amount,S.WHT,'DZ' as  Type " & vbCrLf
            sql &= " from SAP_Income S,Customer C " & vbCrLf
            sql &= " where '0000' + S.IM_CUSTOMER = C.CUSTOMER_no  " & vbCrLf
            If (Customer <> "") Then
                sql &= " AND C.Customer_no Like '%' + @Customer + '%'  " & vbCrLf
            End If
            If (Bank <> "0") Then
                sql &= " AND S.Item_Text Like '%' + @Bank + '%' " & vbCrLf
            End If
            If (Type_Pay <> "2") Then
                sql &= " AND S.Type_Pay = @Type_Pay " & vbCrLf
            End If
        Else
            sql = "select Convert(VARCHAR,Convert(DATETIME,A.Value_Date),106) as Value_Date,M.Customer as Customer_Code,C.NAME1 + C.NAME2 + C.NAME3 as Customer_Name,A.Ex_belnr as RV_no,A.Item_Text + ' ' + A.Gl_Account as BANK " & vbCrLf
            sql &= " , A.Amount,A.WHT,'DY' as  Type " & vbCrLf
            sql &= " from SAP_Advanced_Payment A,SAP_Advanced_Payment_Material M,Customer C " & vbCrLf
            sql &= " where A.Iport_no = M.Iport_no " & vbCrLf
            sql &= " and '0000' + M.Customer = C.Customer_no " & vbCrLf
            sql &= " And A.Value_Date  Between " & DateFrom & " and " & Date_To & " " & vbCrLf
            If (Customer <> "") Then
                sql &= " AND C.Customer_no Like '%' + @Customer + '%'  " & vbCrLf
            End If
            If (Bank <> "0") Then
                sql &= " AND A.Item_Text Like '%' + @Bank + '%' " & vbCrLf
            End If
            If (Type_Pay <> "2") Then
                sql &= " AND A.Type_Pay = @Type_Pay " & vbCrLf
            End If
            sql &= " UNION " & vbCrLf
            sql &= " select Convert(VARCHAR,Convert(DATETIME,S.Value_Date),106) as Value_Date,S.IM_CUSTOMER as Customer_Code,C.NAME1 + C.NAME2 + C.NAME3 as Customer_Name,S.Ex_belnr as RV_no,S.Item_Text + ' ' + S.Gl_Account as BANK " & vbCrLf
            sql &= "  ,S.Amount,S.WHT,'DZ' as  Type " & vbCrLf
            sql &= " from SAP_Income S,Customer C " & vbCrLf
            sql &= " where '0000' + S.IM_CUSTOMER = C.CUSTOMER_no  " & vbCrLf
            If (Customer <> "") Then
                sql &= " AND C.Customer_no Like '%' + @Customer + '%'  " & vbCrLf
            End If
            If (Bank <> "0") Then
                sql &= " AND S.Item_Text Like '%' + @Bank + '%' " & vbCrLf
            End If
            If (Type_Pay <> "2") Then
                sql &= " AND S.Type_Pay = @Type_Pay " & vbCrLf
            End If
        End If

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Customer", SqlDbType.NVarChar, 200).Value = Customer
        adap.SelectCommand.Parameters.Add("@Bank", SqlDbType.NVarChar, 50).Value = Bank
        adap.SelectCommand.Parameters.Add("@Type_Pay", SqlDbType.NVarChar, 1).Value = Type_Pay
        adap.Fill(ds, "Pay")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_MshipBulk(ByVal DateFrom As String, ByVal Date_To As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select Booking.Booking_no,Sum(Convert(float,_M)) as _M,Vehicle_GRT from Load_Unload_Product_Bulk,Booking " & vbCrLf
        sql &= " where Booking.Booking_no in ( " & vbCrLf
        sql &= "select Booking_no from Load_Unload_Vessel " & vbCrLf
        sql &= " Where Depart_Actual_Date <> '' " & vbCrLf
        sql &= " And '20' + subString(Depart_Actual_Date,7,2) + subString(Depart_Actual_Date,4,2) + subString(Depart_Actual_Date,1,2) Between " & DateFrom & " and " & Date_To & " "
        sql &= " ) "
        sql &= " and Booking.Booking_no =  Load_Unload_Product_Bulk.Booking_no " & vbCrLf
        sql &= " and Booking.Booking_no Not in (select Shift_Booking as Booking_no from BookingWharf where Shift_Booking <> '')"
        sql &= " and Booking.Status = 'FN'"
        sql &= " Group By Booking.Booking_no,Vehicle_GRT " & vbCrLf
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.Fill(ds, "MyBooking")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_PRODUCT_monthly(ByVal Port As String, ByVal DateFrom As String, ByVal Date_To As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select P.Product_no,P.Product_Event,P.Product_Name,L.LU_BLMT,L.BU_TRUCK " & vbCrLf
        sql &= " ,L.LU_StartActual_Date,L.LU_StopActual_Date,L.LU_StartActual_Time,L.LU_StopActual_Time " & vbCrLf
        sql &= ", V.StandardTime, V.ActualTime" & vbCrLf
        sql &= " from Load_Unload_Product L,Booking_Product P,Booking B, vw_Load_Unload_Vessel_Time V " & vbCrLf
        sql &= " where L.Booking_no = P.Booking_no " & vbCrLf
        sql &= " and P.Booking_no = B.Booking_no " & vbCrLf
        sql &= " AND B.Booking_no = V.Booking_no" & vbCrLf
        sql &= " and PORT = '" & Port & "' "
        sql &= " and L.Product_no <> '' " & vbCrLf
        sql &= " And '20' + subString(L.LU_StopActual_Date,7,2) + subString(L.LU_StopActual_Date,4,2) + subString(L.LU_StopActual_Date,1,2) Between " & DateFrom & " and " & Date_To & " "

        sql &= " UNION  " & vbCrLf
        sql &= " select L.Product_no,'L' as Product_Event,L.Product_no as Product_Name,L.LU_BLMT,L.BU_TRUCK " & vbCrLf
        sql &= " ,L.LU_StartActual_Date,L.LU_StopActual_Date,L.LU_StartActual_Time,L.LU_StopActual_Time " & vbCrLf
        sql &= ", V.StandardTime, V.ActualTime" & vbCrLf
        sql &= " from Load_Unload_Product L,Booking B, vw_Load_Unload_Vessel_Time V " & vbCrLf
        sql &= " where L.Booking_no = B.Booking_no " & vbCrLf
        sql &= " AND B.Booking_no = V.Booking_no" & vbCrLf
        sql &= " and PORT = '" & Port & "' "
        sql &= " and L.Product_no <> '' " & vbCrLf
        sql &= " and L.Product_no In ('Bunker FO','Bunker DO')"
        sql &= " And '20' + subString(L.LU_StopActual_Date,7,2) + subString(L.LU_StopActual_Date,4,2) + subString(L.LU_StopActual_Date,1,2) Between " & DateFrom & " and " & Date_To & " "
        sql &= " order by Product_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.Fill(ds, "Total")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_OCC_monthly(ByVal Wharf As String, ByVal DateFrom As String, ByVal Date_To As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select Load_unload_vessel.Depart_actual_Date,Load_unload_vessel.Depart_actual_Time,Booking_LogSheet_In.Berthing_Date,Booking_LogSheet_In.Berthing_TimeTo " & vbCrLf
        sql &= " from BookingWharf,Wharf,Load_unload_vessel,Booking_LogSheet_In " & vbCrLf
        sql &= " where Wharf.BookingWharf = BookingWharf.BookingWharf " & vbCrLf
        sql &= " and bookingwharf.Booking_no = Load_unload_Vessel.Booking_no " & vbCrLf
        sql &= " and Load_unload_Vessel.Booking_no = Booking_LogSheet_In.Booking_no "
        sql &= " and Wharf.Wharf = '" & Wharf & "' " & vbCrLf
        sql &= " And '20' + subString(Load_unload_vessel.Depart_Actual_Date,7,2) + subString(Load_unload_vessel.Depart_Actual_Date,4,2) + subString(Load_unload_vessel.Depart_Actual_Date,1,2) Between " & DateFrom & " and " & Date_To & " "
        sql &= " And Load_unload_vessel.Depart_actual_time <> '' "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.Fill(ds, "OCC")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_OCC_monthly_ForOVerLab(ByVal Wharf As String, ByVal DateFrom As String, ByVal Date_To As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select B.Berthing_Date,B.Berthing_TimeTo,L.Depart_Actual_Date,L.Depart_Actual_Time " & vbCrLf
        sql &= " from Booking_LogSheet_In B,Load_unload_Vessel L,BookingWharf O,Wharf W " & vbCrLf
        sql &= " where B.Booking_no = L.Booking_no " & vbCrLf
        sql &= " and L.Booking_no = O.Booking_no " & vbCrLf
        sql &= " and O.BookingWharf = W.BookingWharf"
        sql &= " and W.Wharf = '" & Wharf & "' " & vbCrLf
        sql &= " and B.Berthing_Date <> '' " & vbCrLf
        sql &= " and L.Depart_Actual_Date <> '' " & vbCrLf
        sql &= " And ( CONVERT(VARCHAR(10), Convert(DateTime,B.Berthing_Date) , 112) + subString(B.Berthing_TimeTo,1,2) + subString(B.Berthing_TimeTo,4,2) Between " & DateFrom & " and " & Date_To & " " & vbCrLf
        sql &= " And '20' + subString(L.Depart_Actual_Date,7,2) + subString(L.Depart_Actual_Date,4,2) + subString(L.Depart_Actual_Date,1,2) + subString(L.Depart_Actual_Time,1,2) + subString(L.Depart_Actual_Time,4,2) Between " & DateFrom & " and " & Date_To & " )"

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.Fill(ds, "OCC_")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_PRODUCT_monthly_PORT_TIME(ByVal Port As String, ByVal DateFrom As String, ByVal Date_To As String, ByVal Destination As String, Optional ByVal ProductName As String = "") As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select * " & vbCrLf
        sql &= " from Booking_LogSheet_In I,Booking_LogSheet_Out O,Load_Unload_Vessel L,Booking B,BookingWharf W,Wharf H " & vbCrLf
        sql &= " Where B.PORT = '" & Port & "' "
        If (Destination <> "") Then
            sql &= " and B.Vehicle_Destination = '" & Destination & "' " & vbCrLf
        End If
        sql &= " and B.Booking_no = I.Booking_no " & vbCrLf
        sql &= " and B.Booking_no = O.Booking_no " & vbCrLf
        sql &= " and B.Booking_no = L.Booking_no " & vbCrLf
        sql &= " and B.BOoking_no  = W.BOoking_no " & vbCrLf
        sql &= "  and W.BookingWharf = H.BOokingWharf " & vbCrLf
        sql &= " And '20' + subString(L.Depart_Actual_Date,7,2) + subString(L.Depart_Actual_Date,4,2) + subString(L.Depart_Actual_Date,1,2) Between " & DateFrom & " and " & Date_To & " "
        If ProductName <> "" Then
            sql &= " AND Product_Name = '" & ProductName & "' "
        End If

        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.Fill(ds, "Total")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_TempEstimateOneRecord(ByVal Booking_No As String, ByVal Mat As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select data4 " & vbCrLf
        sql &= " from Temp_Estimate "
        sql &= " Where Booking_no = @Booking_no "
        sql &= " and Material = @Material"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_No
        adap.SelectCommand.Parameters.Add("@Material", SqlDbType.NVarChar, 18).Value = Mat
        adap.Fill(ds, "Temp")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_ActualOneRecord(ByVal Booking_No As String, ByVal Mat As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select data4 " & vbCrLf
        sql &= " from Actual_Data_Backup_For_Billing "
        sql &= " Where Booking_no = @Booking_no "
        sql &= " and Material = @Material"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_No
        adap.SelectCommand.Parameters.Add("@Material", SqlDbType.NVarChar, 18).Value = Mat
        adap.Fill(ds, "Actual")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_OtherServiceForReportBulk(ByVal Booking_No As String) As DataTable
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select Distinct O.* from OtherService_Head H,OtherService O " & vbCrLf
        sql &= " Where H.Booking_no = @Booking_no "
        sql &= " and H.Ots_ID = O.Ots_ID "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_No
        adap.Fill(ds, "OTs")
        adap = Nothing
        con.Close()
        Return ds.Tables(0)
    End Function

    Public Function Get_EmailForSend(ByVal Role_no As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select UserIPORT.Email from UserIPORT,User_Role " & vbCrLf
        sql &= " where UserIPORT.User_name = User_Role.User_name "
        sql &= " and User_Role.Role_no in (" & Role_no & ")"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.Fill(ds, "OTs")
        adap = Nothing
        con.Close()
        Dim Email As String = ""
        For Each dr As DataRow In ds.Tables(0).Rows
            If (dr.Item("Email") & "" <> "") Then
                If Email = "" Then
                    Email = dr.Item("Email") & ""
                Else
                    Email &= ";" & dr.Item("Email")
                End If
            End If
        Next
        Return Email
    End Function

    Public Function Get_EmailForSendAgent(ByVal Booking_no As String) As String
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = " select Distinct UserIPORT.Email from UserIPORT,Booking" & vbCrLf
        sql &= " where ((UserIPORT.User_name = Booking.Booking_Code) "
        sql &= " OR (UserIPORT.User_name = Booking.MAgent_no)"
        sql &= " OR (UserIPORT.User_name = Booking.SAgent_no)) "
        sql &= " and Booking.Booking_no = @Booking_no "
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@Booking_no", SqlDbType.NVarChar, 10).Value = Booking_no
        adap.Fill(ds, "OTs")
        adap = Nothing
        con.Close()
        Dim Email As String = ""
        For Each dr As DataRow In ds.Tables(0).Rows
            If (dr.Item("Email") & "" <> "") Then
                If Email = "" Then
                    Email = dr.Item("Email") & ""
                Else
                    Email &= ";" & dr.Item("Email")
                End If
            End If
        Next
        Return Email
    End Function

    Public Function CheckExpire() As Boolean
        ' 2A0D830290F110
        Try
            Dim Day As Integer = Expire.Substring(13, 1) & Expire.Substring(0, 1)  '1
            Dim Month As Integer = Expire.Substring(2, 1) & Expire.Substring(4, 1)   '3,5
            Dim Year As Integer = Expire.Substring(7, 1) & Expire.Substring(9, 1) & Expire.Substring(11, 2) '8,10,12
            If (CInt(Date.Now.ToString("yyyy", New CultureInfo("en-US"))) = Year) Then
                If (CInt(Date.Now.ToString("MM")) = Month) Then
                    If (CInt(Date.Now.ToString("dd")) = Day) Then
                        Return True   'Expire
                    ElseIf (CInt(Date.Now.ToString("dd")) > Day) Then
                        Return True   'Expire
                    Else
                        Return False
                    End If
                ElseIf (CInt(Date.Now.ToString("MM")) > Month) Then
                    Return True   'Expire
                Else
                    Return False
                End If
            ElseIf (CInt(Date.Now.ToString("yyyy", New CultureInfo("en-US"))) > Year) Then
                Return True   'Expire
            Else
                Return False
            End If
        Catch ex As Exception
            Return True   'Expire
        End Try
    End Function

    Public Function Get_TableColumnConfigure(ByVal UserName As String, ByVal PageName As String, ByVal TableName As String) As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT     UserName, PageName, TableName, Header_ID, Header_Index, Header_Status" & vbCrLf
        sql &= "FROM         dbo.TableColumn_Configure" & vbCrLf
        sql &= "WHERE UserName =  @UserName" & vbCrLf
        sql &= "AND PageName = @PageName" & vbCrLf
        sql &= "AND TableName = @TableName" & vbCrLf
        sql &= "ORDER BY Header_ID, Header_Index"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = UserName.Trim()
        adap.SelectCommand.Parameters.Add("@PageName", SqlDbType.NVarChar, 50).Value = PageName.Trim()
        adap.SelectCommand.Parameters.Add("@TableName", SqlDbType.NVarChar, 50).Value = TableName.Trim()
        adap.Fill(ds, "TableColumnConfigure")
        adap = Nothing
        con.Close()
        Return ds
    End Function

    Public Function Get_TableColumnIndex(ByVal UserName As String, ByVal PageName As String, ByVal TableName As String) As DataSet
        Dim con As New SqlConnection(connString)
        Dim adap As New SqlDataAdapter()
        Dim sql As String
        Dim ds As New DataSet
        sql = "SELECT     UserName, PageName, TableName, Header_ID, Header_Index, Header_Status" & vbCrLf
        sql &= "FROM         dbo.TableColumn_Configure" & vbCrLf
        sql &= "WHERE UserName =  @UserName" & vbCrLf
        sql &= "AND PageName = @Booking_no" & vbCrLf
        sql &= "AND TableName = @Booking_no" & vbCrLf
        sql &= "ORDER BY Header_ID, Header_Index"
        adap.SelectCommand = New SqlCommand(sql, con)
        adap.SelectCommand.Parameters.Clear()
        adap.SelectCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = UserName.Trim()
        adap.SelectCommand.Parameters.Add("@PageName", SqlDbType.NVarChar, 50).Value = PageName.Trim()
        adap.SelectCommand.Parameters.Add("@TableName", SqlDbType.NVarChar, 50).Value = TableName.Trim()
        adap.Fill(ds, "TableColumnIndex")
        adap = Nothing
        con.Close()
        Return ds
    End Function

End Class